software

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: www.excelforum.com/member.php...oamp;userid=32274
View this thread: www.excelforum.com/showthread...hreadid=521940Gohan51D wrote:
gt; Cells D2-D15 contains a list of dates ranging from Jan through May. I
gt; need D16 to count how many cells in that column contain dates in
gt; March. I am assuming it is a COUNTIF function but I have not been
gt; able to find a solution in any texts or on the internet. Any type of
gt; help is appreciated.
gt;
gt; Gohan51D

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

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))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=521940
Thanks I will give them a try

Gohan51D--
Gohan51D
------------------------------------------------------------------------
Gohan51D's Profile: www.excelforum.com/member.php...oamp;userid=32274
View this thread: www.excelforum.com/showthread...hreadid=521940
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: www.excelforum.com/member.php...oamp;userid=32274
View this thread: www.excelforum.com/showthread...hreadid=521940Maybe 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 wrote:
gt;
gt; The formula seems to work but I didn't mention that the column of dates
gt; also contains text. When I remove the text the formula works great but
gt; once the text is entered I get #VALUE!, how can I ignore all text.
gt;
gt; Gohan51D
gt;
gt; --
gt; Gohan51D
gt; ------------------------------------------------------------------------
gt; Gohan51D's Profile: www.excelforum.com/member.php...oamp;userid=32274
gt; View this thread: www.excelforum.com/showthread...hreadid=521940

--

Dave Peterson


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--
Gohan51D
------------------------------------------------------------------------
Gohan51D's Profile: www.excelforum.com/member.php...oamp;userid=32274
View this thread: www.excelforum.com/showthread...hreadid=521940What happens when you tried the formula I suggested?

Gohan51D wrote:
gt;
gt; The formula seems to work but I didn't mention that the column of dates
gt; also contains text. When I remove the text the formula works great but
gt; once the text is entered I get #VALUE!, how can I ignore all text.
gt;
gt; This is the formula used. If any text in D2-D19 then I get #VALUE!
gt; If not text it works great.
gt;
gt; =SUMPRODUCT(--ISNUMBER(D219),--(MONTH(D219)=2)*1)
gt;
gt; =SUM(IF(MONTH(E2:E18)=2,1,0)) This also worked but with the same text
gt; problem.
gt;
gt; Gohan51D
gt;
gt; --
gt; Gohan51D
gt; ------------------------------------------------------------------------
gt; Gohan51D's Profile: www.excelforum.com/member.php...oamp;userid=32274
gt; View this thread: www.excelforum.com/showthread...hreadid=521940

--

Dave Peterson


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--
Gohan51D
------------------------------------------------------------------------
Gohan51D's Profile: www.excelforum.com/member.php...oamp;userid=32274
View this thread: www.excelforum.com/showthread...hreadid=521940I don't think you need that *1. The -- stuff changes the trues and falses to
1's and 0's.
Gohan51D wrote:
gt;
gt; This is the one that finally worked for me
gt;
gt; =SUM(IF(--ISNUMBER(D218),--(MONTH(D218)=3)*1)) You were right
gt; Dave
gt;
gt; Thanks for the help it is greatly appreciated.
gt;
gt; Gohan51D
gt;
gt; --
gt; Gohan51D
gt; ------------------------------------------------------------------------
gt; Gohan51D's Profile: www.excelforum.com/member.php...oamp;userid=32274
gt; View this thread: www.excelforum.com/showthread...hreadid=521940

--

Dave Peterson