J
James
My data is:
Date Time Calls Offered
8/21/2007 10:00:00AM 2
8/21/2007 11:00:00AM 4
8/21/2007 11:30:00AM 2
8/21/2007 12:30:00PM 1
8/21/2007 1:00:00PM 2
8/21/2007 3:30:00PM 1
8/21/2007 4:00:00PM 1
etc.
Note that the data is down to the 30min, but not all times are listed. Since
I have to use the data across multiple weeks, I want to use sumproduct as a
generic formula to grab numbers and put them into a pivotable table. My
receiving tab is as such:
A B C D
Time 8/21/2007 8/22/2007 8/23/2007 etc
12:00:00AM
12:30:00AM
1:00:00AM
1:30:00AM
2:00:00AM
2:30:00AM
3:00:00AM
3:30:00AM
4:00:00AM
etc. to 11:30pm
I tried using:
=SUMPRODUCT(--((Data!$B$2:$B$65500)=$B2),--((Data!$A$2:$A$65500)=$A2),(Data!$C$2:$C$65500))
Where the "Data" tab has dates in B2:B65500 and times in A2:A65500 and calls
offered numbers in C2:C65500. When I use this formula I get zeroes even in
cells that clearly have a number in there. I've tried different formats but
still I get zero.
Am I doing something wrong?
Date Time Calls Offered
8/21/2007 10:00:00AM 2
8/21/2007 11:00:00AM 4
8/21/2007 11:30:00AM 2
8/21/2007 12:30:00PM 1
8/21/2007 1:00:00PM 2
8/21/2007 3:30:00PM 1
8/21/2007 4:00:00PM 1
etc.
Note that the data is down to the 30min, but not all times are listed. Since
I have to use the data across multiple weeks, I want to use sumproduct as a
generic formula to grab numbers and put them into a pivotable table. My
receiving tab is as such:
A B C D
Time 8/21/2007 8/22/2007 8/23/2007 etc
12:00:00AM
12:30:00AM
1:00:00AM
1:30:00AM
2:00:00AM
2:30:00AM
3:00:00AM
3:30:00AM
4:00:00AM
etc. to 11:30pm
I tried using:
=SUMPRODUCT(--((Data!$B$2:$B$65500)=$B2),--((Data!$A$2:$A$65500)=$A2),(Data!$C$2:$C$65500))
Where the "Data" tab has dates in B2:B65500 and times in A2:A65500 and calls
offered numbers in C2:C65500. When I use this formula I get zeroes even in
cells that clearly have a number in there. I've tried different formats but
still I get zero.
Am I doing something wrong?