Sumproduct with date and time?

[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 nubmers 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?

 

[T. Valko]

This works for me:

=SUMPRODUCT(--(Data!$A$2:$A$8=B$1),--(Data!$B$2:$B$8=$A2),Data!$C$2:$C$8)

Copied across then down.

Note that your times are not true Excel times, they're TEXT strings. True
Excel times have a space before the AM/PM:

10:00:00 AM

It will still work as TEXT strings, though.