counting the times days are used in a month

[rustybenson]

Hi
I have a list of dates down a work sheet. Some dates appear more than once. I want to count how many times a date appears per month for a 12 month period.
a1; 1/7/2010
a2; 2/7/2010
a3; 10/7/2010
a4; /8/2010
a5; 15/8/2010
a6; 29/9/2010
a7; 30/9/2010
a8; 30/9/2010

Seventh month has 3 dates
eighth month has 2 dates
nineth month has 3 dates.

Can this be done?
Thanks for any help provided.
Regards Rusty

 

[Aladin Akyurek]

I have checked . They are in as dates ( Australia Date DD/MM/YYYY)

So what do you get with:

=SUMPRODUCT(ISNUMBER(A1:A8)+0)

and

=COUNTIF(A1:A8,"?*")

 

[T. Valko]

Hi
Thanks for the replies.
I have tried both answers. The first one works fine however if I have the column formated to date and a zero entry it counts the 1/1/1900 in the month 1 total.
The second reply gives me a "value" error.
Regards
Rusty

Try this tweak to the first formula...

=SUMPRODUCT(--($A$1:$A$8<>""),--(MONTH($A$1:$A$8)=C2))

 

[AlphaFrog]

This should not count the zero dates...
=SUMPRODUCT(--(MONTH($A$1:$A$8)=C2), --($A$1:$A$8>0))

 

[rustybenson]

=SUMPRODUCT(ISNUMBER(A1:A8)+0) =8 as a reply
The countif =0 as the reply

 

[Aladin Akyurek]

=SUMPRODUCT(ISNUMBER(A1:A8)+0) =8 as a reply
The countif =0 as the reply

Russell,

That means we have true dates in A1:A8. Given that, the set up in post #3 with

=SUMPRODUCT(($A$1:$A$8-DAY($A$1:$A$8)+1=("1-"&E$1&"-"&$D2)+0)+0)

should work as intended. Would you have another look at it?