Count number of cells of a particular month in a column of dates

[Gohan51D]

Cells D2-D15 contains a list of dates ranging from Jan through May. I
need D16 to count how many cells in that column contain dates in March.
I am assuming it is a COUNTIF function but I have not been able to find
a solution in any texts or on the internet. Any type of help is
appreciated.

Gohan51D

 

[Paul Lautman]

Cells D2-D15 contains a list of dates ranging from Jan through May. I
need D16 to count how many cells in that column contain dates in
March. I am assuming it is a COUNTIF function but I have not been
able to find a solution in any texts or on the internet. Any type of
help is appreciated.

Gohan51D

=SUMPRODUCT((MONTH(D215)=3)*1)

 

[daddylonglegs]

If you don't care about the year you could use

=SUMPRODUCT(--(MONTH(D215)=3))

where 3 = March

be careful if you try to count for January because blank cells will be
included in the count so you need to alter to

=SUMPRODUCT(--ISNUMBER(D215),--(MONTH(D215)=1))

 

[Gohan51D]

Thanks I will give them a try

Gohan51D

 

[Gohan51D]

The formula seems to work but I didn't mention that the column of dates
also contains text. When I remove the text the formula works great but
once the text is entered I get #VALUE!, how can I ignore all text.

Gohan51D

 

[Dave Peterson]

Maybe you could use something like:

=SUM(IF(ISNUMBER(D215),--(MONTH(D215)=1)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column

 

[Gohan51D]

The formula seems to work but I didn't mention that the column of dates
also contains text. When I remove the text the formula works great but
once the text is entered I get #VALUE!, how can I ignore all text.

This is the formula used. If any text in D2-D19 then I get #VALUE!
If not text it works great.

=SUMPRODUCT(--ISNUMBER(D219),--(MONTH(D219)=2)*1)

=SUM(IF(MONTH(E2:E18)=2,1,0)) This also worked but with the same text
problem.


Gohan51D

 

[Dave Peterson]

What happens when you tried the formula I suggested?

 

[Gohan51D]

This is the one that finally worked for me

=SUM(IF(--ISNUMBER(D218),--(MONTH(D218)=3)*1)) You were right
Dave

Thanks for the help it is greatly appreciated.

Gohan51D

 

[Dave Peterson]

I don't think you need that *1. The -- stuff changes the trues and falses to
1's and 0's.