sumproduct syntax

**amartino44** · 12-26-2012, 06:31 PM

Hello. I'm new to using sumproducts and I have a few question. I used the following formula (from a how to website on the use of sumproduct) to find a count of Ford cars with an "A" rating:
=SUMPRODUCT((A9:A20="Ford")*(B9:B20="A")*(C9:C20)) on the table below. Question 1) Couldn't I just use a sumifs formula? 2) Why do I need to use the double parentheses after sumproduct? 3) How does the formula know to multiply the third condition (C9:C20) by the previous conditions? Thank you

Ford B 3
Vauxhall C 4
Ford A 2
Ford A 1
Ford D 4
Ford A 3
Ford A 2
Renault A 8
Ford A 6
Ford A 8
Ford A 7
Ford A 6

**Norie** · 12-26-2012, 06:40 PM

1 Yes you could use SUMIFS.

2 The double parentheses close off the formula

3 That's how SUMPRODUCT works, by multiplying the ranges you tell it to.

**jeffreybrown** · 12-26-2012, 06:42 PM

Most definitely SUMIFS, and since you have 2007+ it is way more efficient, but knowing SUMPRODUCT is still appropriate because you will run into situations where the SUMIFS will not work.

=SUMIFS($C$9:$C$20,$A$9:$A$20,"Ford",$B$9:$B$20,"A")

Basically the SUMPRODUCT produces a bunch of TRUE/FALSE and the * converts it into 1/0.

Finally, all the 1/0 are multiplied by the last range which produces the final result.

The best learning tool to see what is happening besides the link I shared earlier...

EVALUATE FORMULA
Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

**amartino44** · 12-26-2012, 07:16 PM

Thanks a lot. Very helpful.

**jeffreybrown** · 12-26-2012, 07:16 PM

You are very welcome. Glad we could help

**Palmetto** · 12-26-2012, 08:58 PM

FYI

Nice detailed explanation of SUMPRODUCT

A very good way to really understand how Excel evaluates a formula is to use the Evaluate Formula feature and step through the formula and see the results return in each argument of a function.

Ribbon Menu > Formulas > Evaluate Formula