Formula to count Month in Dates

[K]

Hi, i have dates in one column cells (please see below)

10/06/2005
24/06/2005
03/08/2005
23/08/2005
18/09/2005
21/09/2005
28/09/2005
30/09/2005
05/10/2005
08/10/2005
18/10/2005
19/10/2005
02/11/2005
22/11/2005
26/11/2005
06/12/2005
16/12/2005
21/12/2005

i need a formula in next cell of each date to count the dates which
have same month like in top two dates i have month 6 so formula should
go throw all dates and see which dates are have month 6 and count them
and count result should come to 2 as we got only two dates which are
in month 6. and fourmula should also do same with other dates. i am
trying to count the total of all those dates which have same months.
Please if anybody can help. i dont need macro

 

[Bob Phillips]

=SUMPRODUCT(--(MONTH(A1:A20)=1))

if any can be blank use

=SUMPRODUCT(--(MONTH(A1:A20)=1),--)A1:A20<>""))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

One way:

B1: =SUMPRODUCT(--(MONTH($A$1:$A$18)=MONTH(A1)))

Copy down as needed.

 

[Mike H]

Put this in a cell and drag down 12 rows for Jan to December

=SUMPRODUCT(--(MONTH($A$1:$A$20)=ROW(A1)))

Mike

 

[K]

One way:

B1:      =SUMPRODUCT(--(MONTH($A$1:$A$18)=MONTH(A1)))

Copy down as needed.






- Show quoted text -

Thanks everyone specially JE Mcgimpsey