Using COUNTIF with MONTH in formula

[Kevin]

Hello,

I have a number of formulas where I use the COUNTIF() formula to
count the number of occurences of a particular item in a range.
Now I would like to use in it conjunction with the MONTH() formula
and a list of dates to count the number of dates that occur in
a particular month.

For example, range A1:A100 contains dates and I want to know how
many of the dates occur in June. Given that MONTH returns numbers
1-12, I've been trying something like this:

COUNTIF(MONTH(A1:A100),6)

but I can't get it to work. I've tried using the CNTL-SHIFT-ENTER
to get this to work as an array formula, but no luck.

Any help would be appreciated.

Thanks,
Kevin
(take the "_" out of return email address)

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(MONTH(A1:A100)=6))

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Domenic]

Hi Kevin,

Using COUNTIF, try,

=COUNTIF(A1:A10,">="&DATE(2004,6,1))-COUNTIF(A1:A10,">"&DATE(2004,6,30))

Using SUMPRODUCT, try

=SUMPRODUCT(--(MONTH(A1:A10)=6),--(YEAR(A1:A10)=2004))

Hope this helps!

 

[NickMinUK]

Could insert column, use Month() in new column, then use countif on ne
column with result in original column so you can hide new column. No
fancy, but it works. See attache

Attachment filename: count months.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58026

 

[Kevin]

Thanks, that does exactly what I need.

The response by Peo,
=SUMPRODUCT(--(MONTH(A1:A100)=6))
mostly works, but I also have blank cells and the MONTH()
formula on a blank cell results in 1 which counts toward
the January total.

Your response has the added benefit of being able to
specify the year.

Thanks again.

Kevin

 

[sokevin]

what is the syntax of sumproduct??

i do shift f3 but there is no sumproduct listed

thanks

 

[Norman Harker]

Hi Sokevin!

SUMPRODUCT is under the Math function category:

=SUMPRODUCT(array1,array2,array3, ...)

 

[Grace Lee]

Hi,
I'm Grace here.

May I know that why the formula stated below cannot work?
=COUNTIF('2022 Delivery Sheet'!$A$2:$A,"MONTH(Jan22)")