Counting data in a spreadsheet

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

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)*(Sheet1!$B$2:$B$1000>B$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 <at>yahoo<dot>com
----
"Jo" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

And for a cleaner look in Sheet2,
we could suppress the extraneous zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Thank you for all your help tonight. It is working quite=20
well, except, the formula is not picking up any numbers=20
from my column D times (2300 - 0700). Any ideas?
>-----Original Message-----
>One way
>
>Assuming the table below is in Sheet1 cols A to C,
>data from row2 down, where col B =3D time, col C =3D 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:
>
>=3DSUMPRODUCT((LEFT(Sheet1!$A$2:$A$1000,2)=3D$A$1)*(Sheet1!
$B$2:$B$1000>B$1)*(Sh
>eet1!$B$2:$B$1000<=3DB$2)*(Sheet1!$C$2:$C$1000=3D$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=20
SUMPRODUCT)
>
>Just change the input in A1 from "JO" to "JE" to get the=20
counts for JE
>(or just duplicate the Sheet2 and use the duplicate=20
for "JE"'s figures)
>
>--
>Rgds
>Max
>xl 97
>---
>GMT+8, 1=B0 22' N 103=B0 45' E
>xdemechanik <at>yahoo<dot>com
>----
>"Jo" <[email protected]> wrote in message
>news:[email protected]...
>> 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,=20
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
>>
>>
>
>
>.
>

> ... the formula is not picking up any numbers
> from my column D times (2300 - 0700). Any ideas?

Oops, think the formula in D3 needs to be revised

Instead of copying across B3 to D3,
just copy B3 across to C3

Put in D3 (revised formula):

=SUMPRODUCT((LEFT(Sheet1!$A$2:$A$1000,2)=$A$1)*((Sheet1!$B$2:$B$1000>D$1)+(S
heet1!$B$2:$B$1000<=D$2))*(Sheet1!$C$2:$C$1000=$A3))

Then select B3:D3 and fill down

Col D should be ok now
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"[email protected]" <[email protected]> wrote in
message news:[email protected]...
Thank you for all your help tonight. It is working quite
well, except, the formula is not picking up any numbers
from my column D times (2300 - 0700). Any ideas?

Thanks for all your help. Everything looks quite good,=20
except the formula in column D is not picking up those=20
times 2300 - 0700. Any idea why?
>-----Original Message-----
>And for a cleaner look in Sheet2,
>we could suppress the extraneous zeros from showing via:
>Tools > Options > View tab > Uncheck "Zero values" > OK
>
>--
>Rgds
>Max
>xl 97
>---
>GMT+8, 1=B0 22' N 103=B0 45' E
>xdemechanik <at>yahoo<dot>com
>----
>
>
>.
>

Posted this response in the other branch earlier ..

> ... the formula is not picking up any numbers
> from my column D times (2300 - 0700). Any ideas?

Oops, think the formula in D3 needs to be revised

Instead of copying across B3 to D3,
just copy B3 across to C3

Put in D3 (revised formula):

=SUMPRODUCT((LEFT(Sheet1!$A$2:$A$1000,2)=$A$1)*((Sheet1!$B$2:$B$1000>D$1)+(S
heet1!$B$2:$B$1000<=D$2))*(Sheet1!$C$2:$C$1000=$A3))

Then select B3:D3 and fill down

Col D should be ok now

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Jo <[email protected]> wrote in message
news:[email protected]...
Thanks for all your help. Everything looks quite good,
except the formula in column D is not picking up those
times 2300 - 0700. Any idea why?

PERFECT!! Thanks very much Max! =20
>-----Original Message-----
>> ... the formula is not picking up any numbers
>> from my column D times (2300 - 0700). Any ideas?
>
>Oops, think the formula in D3 needs to be revised
>
>Instead of copying across B3 to D3,
>just copy B3 across to C3
>
>Put in D3 (revised formula):
>
>=3DSUMPRODUCT((LEFT(Sheet1!$A$2:$A$1000,2)=3D$A$1)*((Sheet1!
$B$2:$B$1000>D$1)+(S
>heet1!$B$2:$B$1000<=3DD$2))*(Sheet1!$C$2:$C$1000=3D$A3))
>
>Then select B3:D3 and fill down
>
>Col D should be ok now
>--
>Rgds
>Max
>xl 97
>---
>GMT+8, 1=B0 22' N 103=B0 45' E
>xdemechanik <at>yahoo<dot>com
>----
>"[email protected]"=20
<[email protected]> wrote in
>message news:[email protected]...
>Thank you for all your help tonight. It is working quite
>well, except, the formula is not picking up any numbers
>from my column D times (2300 - 0700). Any ideas?
>
>
>.
>

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Jo" <[email protected]> wrote in message
news:[email protected]...
PERFECT!! Thanks very much Max!