Home |
Search |
Today's Posts |
#1
|
|||
|
|||
count date ranges
hi there,
i've column with date date 25/06/2005 26/06/2005 05/07/2005 i just want the total counts for the months for egs. june = 2 and july =1 in this case regards murtaza |
#2
|
|||
|
|||
One play ..
Assume the col of dates is in Sheet1, A1:A100 In Sheet2, put In A1: =ROWS($A$1:A1) In B1: =SUMPRODUCT((Sheet1!$A$1:$A$100<"")*(MONTH(Sheet1 !$A$1:$A$100)=A1)) Select A1:B1, fill down to B12 Col A simply returns the month number (Jan=1, Feb=2, etc) while col B returns the desired counts for the months Adapt the ranges to suit .. For a cleaner look in Sheet2, we could switch off extraneous zeros display in the sheet via clicking: Tools Options View tab Uncheck "Zero values" OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "murtaza" wrote in message ... hi there, i've column with date date 25/06/2005 26/06/2005 05/07/2005 i just want the total counts for the months for egs. june = 2 and july =1 in this case regards murtaza |
#3
|
|||
|
|||
With dates in A1 to A25,
Enter "Jan" (no quotes) in B1, and copy down to get the 12 months listed in the 3 character format. Enter this formula in C1, and copy down to C12: =SUMPRODUCT(--(TEXT(A1:A25,"mmm")=B1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "murtaza" wrote in message ... hi there, i've column with date date 25/06/2005 26/06/2005 05/07/2005 i just want the total counts for the months for egs. june = 2 and july =1 in this case regards murtaza |
#4
|
|||
|
|||
=SUMPRODUCT(--(TEXT(A1:A25,"mmm")=B1))
Neater ! ... but think the range A1:A25 needs to be fixed: $A$1:$A$25 before copying down from C1 <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
You're right Max.<g
BUT, I also forget, as you *didn't*, to include the possibility that the entire referenced range might not be completely populated, and therefore return an incorrect answer. =SUMPRODUCT((TEXT($A$1:$A$25,"mmm")=B1)*($A$1:$A$2 5<"")) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Max" wrote in message ... =SUMPRODUCT(--(TEXT(A1:A25,"mmm")=B1)) Neater ! ... but think the range A1:A25 needs to be fixed: $A$1:$A$25 before copying down from C1 <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells based on date range in another column | New Users to Excel | |||
How do I count cells in a column of dates between date ranges? | Excel Worksheet Functions | |||
Formula that will count between dates ranges | Excel Worksheet Functions | |||
Formula that will count between dates ranges | Excel Worksheet Functions | |||
Count data entries and date problem | Excel Worksheet Functions |