count dates of a certain month in column

[smking204]

I have a column of dates, and I want a formula to return a count of how many dates of, say, April are in the column. I want it to look something like this:

=COUNTIF(A:A, MONTH()=4)

But that isn't working, in any of the variations I've tried. Hoping for a simple solution. Thanks!

 

[alansidman]

Provide a sample of your data (10-12 records) using XL2BB

 

[Rick Rothstein]

Personally, I do not like full column references, but since you are using it, give this formula a try...

=SUM((MONTH(A:A)=4)*(A:A<>""))

 

[Burrgogi]

=SUM((MONTH(A:A)=4)*(A:A<>""))

Trying to improve my formula knowledge a bit here. I understand the first part, but what does that last part mean? *(A:A<>"") and why is it necessary?

 

[Rick Rothstein]

It has to do with your using full column referencing... the MONTH of a blank cell evaluates to 1, so we need to exclude all those extra blank spaces from the SUM function. If a cell in A:A is blank, that expression will return 0, otherwise it evaluates to 1... those numbers are then multiplied by the test for MONTH equal to 4.

 

[Burrgogi]

It has to do with your using full column referencing... the MONTH of a blank cell evaluates to 1, so we need to exclude all those extra blank spaces from the SUM function. If a cell in A:A is blank, that expression will return 0, otherwise it evaluates to 1... those numbers are then multiplied by the test for MONTH equal to 4.

Cool - thanks for the explanation.

By the way, I thought that the OP was on the right track with his original formula. Is there no feasible solution using the COUNTIF and MONTH function together?

 

[Rick Rothstein]

I don't think COUNTIF will work because its second argument is not supposed to be a logical expression.

 

[Burrgogi]

I see, thanks.