Is this possible? SUMPRODUCT with criteria range?

[Cita]

Hello again-
I have the following categories:
(TIME) 12/31/2007 1/1/2008 1/2/2008
A 0800 1959
B 2000 0759
C 0000 1159
D 1200 2359
E 0700 1900
F 1900 0700

I also have 2 columns, 1 has a date and 1 the time. I want to count how many
records meet the (1) date criteria and (2) fall within the time range...is
this possible? I've used SUMPRODUCT before but never with a conditional like
this.

Thanks!

 

[Bob Phillips]

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20>=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2))))

 

[Cita]

Thanks...but I can't get it to work. Maybe it's because I haven't used these
functions before. I keep getting the "#VALUE!" error when I try to modify it.

Thanks again...

 

[Bob Phillips]

Tell me how and why you are modifying it.

 

[Cita]

The only things I changed were the cell references ie my data ranges to be
analyzed are in F and G and the criteria (ranges) are in J and K...

 

[Bob Phillips]

Post it.

 

[Cita]

Here it is...

A B C D E F G H I J
K L
xx xx xx xx xx 12/31 0040 12/31
01/01 01/02
12/31 1720 0900 2059 * * *
2100 0859 *
* *

Columns F and G hold the data I have to sort through using the SUMPRODUCT
function. H and I hold the "categories" or ranges that column G need to fall
between to be classified under the corresponding date (J, K, L, etc.). In
other words, the entry in F1/G1 would be counted for the "2100/0859" category
in column J...
Does this help?