Counting data in a spreadsheet

  • Thread starter Thread starter Jo
  • Start date Start date
J

Jo

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
Click to expand...
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)
 
And for a cleaner look in Sheet2,
we could suppress the extraneous zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK
 
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?
 
... the formula is not picking up any numbers
from my column D times (2300 - 0700). Any ideas?
Click to expand...

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
 
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?
 
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?
Click to expand...

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
 
PERFECT!! Thanks very much Max!
-----Original Message-----

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
Click to expand...
 
Back
Top