Countif w/ Criteria

[carl]

My data is arranged as so:

Date Time Product
20050208 93034 S1
20050208 93035 A6
20050208 93035 O0
20050208 93035 I1
20050208 93035 E5
20050208 93036 A5
20050208 93236 S7
20050208 93236 B7
20050208 93237 X3


Would like a formula to place in ColB of the table below that counts the
number of products displayed for a given day between the times in ColA. For
example, B3 would be counting the number of products between 93030 and 93100
including 93100.
20050208
93030 0
93100 6
93130 0
93200 0
93230 0
93300 3

Thank you in advance.

 

[Max]

One way

Assume this table is in Sheet1, A1:C10
(dates in col A are assumed "real" dates)

Date Time Product
20050208 93034 S1
20050208 93035 A6
20050208 93035 O0
20050208 93035 I1
20050208 93035 E5
20050208 93036 A5
20050208 93236 S7
20050208 93236 B7
20050208 93237 X3

And this table below is in Sheet2, A1:B7

20050208
93030 0
93100 6
93130 0
93200 0
93230 0
93300 3

Put in B2:

=SUMPRODUCT((Sheet1!$A$2:$A$10=$B$1)*(Sheet1!$B$2:$B$10<A2))

Put in B3:

=SUMPRODUCT((Sheet1!$A$2:$A$10=$B$1)*(Sheet1!$B$2:$B$10>=A2)*(Sheet1!$B$2:$B
$10<A3))

Copy B3 down to B7

This'll return the results indicated