count date ranges

M

murtaza

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
 
M

Max

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
 
R

Ragdyer

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))
 
M

Max

=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>
 
R

RagDyeR

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$25<>""))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------



Max said:
=SUMPRODUCT(--(TEXT(A1:A25,"mmm")=B1))
Click to expand...

Neater !

... but think the range A1:A25 needs to be fixed: $A$1:$A$25
before copying down from C1 <g>
 
Top