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

[daddylonglegs]

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

=SUMPRODUCT(--(MONTH(D2:D15)=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(D2:D15),--(MONTH(D2:D15)=1))

[Paul Lautman]

Gohan51D wrote:
> 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(D2:D15)=3)*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.

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

=SUMPRODUCT(--ISNUMBER(D2:D19),--(MONTH(D2:D19)=2)*1)

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


Gohan51D

[Dave Peterson]

Maybe you could use something like:

=SUM(IF(ISNUMBER(D2:D15),--(MONTH(D2:D15)=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 wrote:
>
> 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
>
> --
> Gohan51D
> ------------------------------------------------------------------------
> Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274
> View this thread: http://www.excelforum.com/showthread...hreadid=521940

--

Dave Peterson

[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(D2:D19),--(MONTH(D2:D19)=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 wrote:
>
> 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(D2:D19),--(MONTH(D2:D19)=2)*1)
>
> =SUM(IF(MONTH(E2:E18)=2,1,0)) This also worked but with the same text
> problem.
>
> Gohan51D
>
> --
> Gohan51D
> ------------------------------------------------------------------------
> Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274
> View this thread: http://www.excelforum.com/showthread...hreadid=521940

--

Dave Peterson

[Gohan51D]

This is the one that finally worked for me

=SUM(IF(--ISNUMBER(D2:D18),--(MONTH(D2:D18)=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.



Gohan51D wrote:
>
> This is the one that finally worked for me
>
> =SUM(IF(--ISNUMBER(D2:D18),--(MONTH(D2:D18)=3)*1)) You were right
> Dave
>
> Thanks for the help it is greatly appreciated.
>
> Gohan51D
>
> --
> Gohan51D
> ------------------------------------------------------------------------
> Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274
> View this thread: http://www.excelforum.com/showthread...hreadid=521940

--

Dave Peterson