ExcelBanter - Counting data in a spreadsheet

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Counting data in a spreadsheet (https://www.excelbanter.com/new-users-excel/11699-counting-data-spreadsheet.html)

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)*(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

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 <atyahoo<dotcom
----

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$1000B$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 <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,=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)*((S heet1!$B$2:$B$1000D$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 <atyahoo<dotcom
----
" wrote in
message ...
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 <atyahoo<dotcom
----

.

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)*((S heet1!$B$2:$B$1000D$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 <atyahoo<dotcom
----
Jo wrote in message
...
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$1000D$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 <atyahoo<dotcom
----
"=20
wrote in
message ...
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 <atyahoo<dotcom
----
"Jo" wrote in message
...
PERFECT!! Thanks very much Max!