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

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

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

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

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