count the occurrences of a month in a date&time cell

[Tom]

Hi,

I'm trying to count the occurrences of any given month in a rage where the
cells are in a date&time format. (11/02/2008 11:22:53)

For example in the range A1:A30 I want to count the number of times the
month of February occurs.

I've tried a count(if(mid formula but my brain melted after the 10 attempt.

Any help would be greatly appreciated.

Thanks,

 

[Max]

For example in the range A1:A30 I want to count the number of times the

month of February occurs.

Try: =SUMPRODUCT((MONTH(A1:A30)=2)*(A1:A30<>""))

---

 

[Roger Govier]

Hi Tom
One way
=SUMPRODUCT(--(TEXT(A1:A30,"yymmm")="08Feb"))

 

[David Biddulph]

Do you need the <>"" test? Would you satisfy the MONTH=2 test if column A
had =""?

 

[Roger Govier]

Hi David
I would think Max was generalising, in case the OP tried it for January as
well, as all blank cells would then be counted (00 Jan 1900)
I tried to eliminate that with my posting by including the year (I agree 08
would pick up 1908, but didn't think that likely for the OP).
Of course it would have been better if I had used
text(date,"yyyymmm")="2008Feb"

 

[Max]

Yes, I was generalizing the expression for the OP, as per Roger's line:

I would think Max was generalising, in case the OP tried it for January as
well, as all blank cells would then be counted (00 Jan 1900)

But perhaps I should have explained it in the response itself for
completeness. Roger, thanks for that favour!

---

 

[Tom]

Hi,

Thank you all.

Both solutions worked without a hitch but I'll be going with Roger's as it
gives the option of including a year as well as a moth.

Thank you,
Tom