Using COUNTIF with MONTH in formula

  • Thread starter Thread starter Kevin
  • Start date Start date
K

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

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

--


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

Regards,

Peo Sjoblom
 
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!
 
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
 
what is the syntax of sumproduct??

i do shift f3 but there is no sumproduct listed

thanks:)
 
Hi Sokevin!

SUMPRODUCT is under the Math function category:

=SUMPRODUCT(array1,array2,array3, ...)
 
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)")
 
Last edited by a moderator:
Back
Top