Sumproduct to sum with two criteria

**firefiend** · 02-19-2010, 01:46 PM

I've attached a photo of the worksheet.

I'm usually pretty good with these functions but this time its kicking my butt. I'm trying to sum the dollars in AI where the calue of AJ = "<30" and the value of AK = "Q309"

< 30 and Q309 are text values and appear exactly that way in the cell.

This is the formula I'm using and I'm getting a #Value error.

=SUMPRODUCT((AJ:AJ="< 30")*(AK:AK="Q309")*AI:AI)

Any Ideas?

Thanks for the help

**DonkeyOte** · 02-19-2010, 01:50 PM

You're seeming using XL2007 in which case you should SUMIFS

=SUMIFS(AI:AI,AJ:AJ,"*<30",AK:AK,"Q309")

You should also avoid entire column references as it will slow your calcs inevitably and will be cause of your #VALUE! error.

The #VALUE! error is generated because of the explicit coercion taking place by means of the * and non-numerics in AI:AI range (eg header).
(the SUMIFS is not affected in the same way - nor would a SUMPRODUCT using a double unary approach)

**firefiend** · 02-19-2010, 02:03 PM

I thought SUMIFS was more appropriate as well but I kept returning a value of 0. What I didn't know was the use of the * for the coercions.

I agree about the full columns; this was just a quick excerise and didn't see a need (at the time) to bother with named ranges, haha.

Thanks, for the help; this fixes it up just right.

**DonkeyOte** · 02-19-2010, 02:08 PM

Originally Posted by firefiend

I thought SUMIFS was more appropriate as well but I kept returning a value of 0. What I didn't know was the use of the * for the coercions.

Yes, the wildcard is required in the SUMIFS because the function will otherwise interpret the criteria of "<30" as a numeric test.

The use of wildcard dictates string based criteria
(Of course if you had values in the column like a<30 which were to be treated differently the above would not work as the a<30 would be included)