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

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


--
Gohan51D
------------------------------------------------------------------------
Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274
View this thread: http://www.excelforum.com/showthread...hreadid=521940

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

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)

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

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))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=521940

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

Thanks I will give them a try

Gohan51D


--
Gohan51D
------------------------------------------------------------------------
Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274
View this thread: http://www.excelforum.com/showthread...hreadid=521940

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

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

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

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

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

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

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

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

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

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

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

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