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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"carl" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>