Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 ---- |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
... 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? |
#6
|
|||
|
|||
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 ---- . |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? . |
#9
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Rainfall Data | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Importing Data From Another Spreadsheet | Excel Discussion (Misc queries) | |||
Entering data on template and then data going to a spreadsheet. | Excel Discussion (Misc queries) | |||
How can I transpose data from a spreadsheet into a form that does. | Excel Worksheet Functions |