counting how many times a certain month appears

  • Thread starter Thread starter MM
  • Start date Start date
M

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
 
Yes, you want to use:
=SUMPRODUCT(--(MONTH('Montreal Office'!A3:A5000)=5))

or

=SUMPRODUCT(--(TEXT('Montreal Office'!A3:A5000,"ddd")="May"))
 
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
 
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
 
Back
Top