One way
Assuming the table below is in Sheet1 cols A to C,
data from row2 down, where col B = time, col C = date
Client # TIME DATE
JO01804/03 855 20-Dec-2003
JO01822/03 1141 20-Dec-2003
JO01825/03 1431 20-Dec-2003
etc
In Sheet2
-------------
Put in A1: JO
Set-up the 3 time bands
List in:
B1:B2 : 700, 1500
C1:C2 : 1500, 2300
D1:D2 : 2300, 700
Put in a starting date in A3, say: 20-Dec-2003
Copy A3 down as desired
Put in B3:
=SUMPRODUCT((LEFT(Sheet1!$A$2:$A$1000,2)=$A$1)*(Sh eet1!$B$2:$B$1000B$1)*(Sh
eet1!$B$2:$B$1000<=B$2)*(Sheet1!$C$2:$C$1000=$A3))
Copy B3 across to D3, then fill down as required
Cols B to D will return the counts for JO
Adapt the ranges to suit
(but note that you can't use entire col references in SUMPRODUCT)
Just change the input in A1 from "JO" to "JE" to get the counts for JE
(or just duplicate the Sheet2 and use the duplicate for "JE"'s figures)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jo" wrote in message
...
I just need to do a count on the data below:
Any client # that begins with JE - I need to know the
total # of JE's for each day of the year.
For the JO clients - I need to know the total # for each
day of the year - but divided into 3 different time
periods - anything between 0701 - 1500 or 1501 - 2300, or
2301 - 0700.
Can anyone help me?
Client # TIME DATE
JO01804/03 855 20-Dec-2003
JO01822/03 1141 20-Dec-2003
JO01825/03 1431 20-Dec-2003
JO01826/03 1551 20-Dec-2003
JO0183/03 753 23-Dec-2003
JO0187/03 809 23-Dec-2003
JO0124/03 834 23-Dec-2003
JO0189/03 1038 23-Dec-2003
JO0186/03 1226 23-Dec-2003
JO01882/03 1533 23-Dec-2003
JO0186/03 1946 23-Dec-2003
JO0187/03 2158 23-Dec-2003
JO01902/03 846 24-Dec-2003
JO0162/03 730 26-Dec-2003
JO09069/03 1432 26-Dec-2003
JO0070/03 1504 26-Dec-2003
JO01978/03 1103 27-Dec-2003
JO01985/03 814 28-Dec-2003
JO01977/03 1133 31-Dec-2003
JO0195/03 1601 31-Dec-2003
JO01986/03 1659 31-Dec-2003
JE00890/03 2226 31-Dec-2003
JE00889/03 1920 31-Dec-2003
JE00888/03 1820 31-Dec-2003
JE00886/03 1517 31-Dec-2003
JE00885/03 1412 31-Dec-2003
JE00884/03 1352 31-Dec-2003
JE00883/03 1308 31-Dec-2003
JE00882/03 1255 31-Dec-2003
JE008481/03 1241 31-Dec-2003