# Using COUNTIF with MONTH in formula

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)

P

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

D

#### 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!

N

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

K

#### 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.

specify the year.

Thanks again.

Kevin

S

#### sokevin

what is the syntax of sumproduct??

i do shift f3 but there is no sumproduct listed

thanks

N

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

Last edited by a moderator: