Using COUNTIF with MONTH in formula

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

"Kevin" wrote in message
...
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)

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!

In article ,
Kevin wrote:

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)

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

Attachment filename: count months.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=580268
---
Message posted from http://www.ExcelForum.com/

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

Domenic wrote:

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!

In article ,
Kevin wrote:


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)

what is the syntax of sumproduct??

i do shift f3 but there is no sumproduct listed

thanks


---
Message posted from http://www.ExcelForum.com/

Hi Sokevin!

SUMPRODUCT is under the Math function category:

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

"sokevin " wrote in message
...
what is the syntax of sumproduct??

i do shift f3 but there is no sumproduct listed

thanks


---
Message posted from http://www.ExcelForum.com/