counting how many times a certain month appears

[MM]

Hi all,

Fourth try's a charm....I'm getting errors trying to post my question. Here
it is...
1 workbook, 4 worksheets each named for a different office.
Column A for each sheet is the date in the following format 03-May-10.
I need to count how many times May appears in column A, range A3:A5000, from
the 'Montreal Office' worksheet. I thought I could use =countif but from
other posts, it seems maybe =sumproduct is the better option. I need your
help. My stats are due pretty soon.

Looking forward to your replies.
MM

 

[Luke M]

Yes, you want to use:
=SUMPRODUCT(--(MONTH('Montreal Office'!A3:A5000)=5))

or

=SUMPRODUCT(--(TEXT('Montreal Office'!A3:A5000,"ddd")="May"))

 

[MM]

That's awesome. It works for me.
Thanks for spelling it out for me. Wasn't quite sure of myself and didn't
want to screw up the rest of my work. Thanks again Luke.

MM

 

[MM]

Hi Luke,
The SUMPRODUCT worked great until I got to Jan. I entered the formula for
our fiscal year (april 2010 to March 2011) and everything after May has a 0
for an answer so far except for Jan. The only one giving me a problem. It's
giving me a very large number. I'm assuming the last number in the formula
is the month (5=May, 4=Apr etc...)
So, I tried the second formula you provided. That solved the problem for
Jan (giving me a 0 for an answer) but screwed up Apr and May by also giving
it a 0.
I would like to keep the same formula for all of the months. Remember, my
date format is 1-May-10. Would the 1 in the day or year affect the outcome?
Something's not right. I need your help.

MM