Sumproduct using Month as one of the criteria

Hi Guys,

I am always getting an error #NUM when I enter the Month function into the SUMPRODUCT formula. Here is the table:

Column A
07/01/2008
08/01/2008
07/03/2008
09/02/2008

Column B
Sales
Tech
Tech
Sales

Column C
Majors
Majors
Majors
National

I have other 3 columns to add as part of my criterias but here is my formula:

=SUMPRODUCT(--(MONTH($A$1:$A$5)=7),--($B$1:$B$5="Sales"),--(!$C$1:$C$5="Majors))

The result should be 1 but I am always getting #NUM.

Your help on this is appreciated!

I did almost the same thing and I got 1, the answer you are looking for. The only thing I changed was I got rid of the exclamation point in front of $C$1 in the last parenthetical term. So my formula was this:

=SUMPRODUCT(--(MONTH($A$1:$A$5)=7),--($B$1:$B$5="Sales"),--($C$1:$C$5="Majors"))

And like I said, this gave me 1 as the result.

I don't think that the MONTH function will cause a #NUM! error. In SUMPRODUCT that could be caused if you try to reference the whole column, your formula has a couple of typos. I assume you are trying

=SUMPRODUCT(--(MONTH($A$1:$A$5)=7),--($B$1:$B$5="Sales"),--($C$1:$C$5="Majors"))

That's a viable formula.....