At first glance, this function can only help you multiplying corresponding components in the given arrays, and returns the sum of those products.
You will be impressed once you know the potential of this not-so-known function.
Here is what you can additionally do with it…
- Conditional Count single (COUNTIF)
- Conditional Count multiple (COUNTIFS)
- Conditional Sum single (SUMIF)
- Conditional Sum multiple (SUMIFS)
- Vlookup multiple criteria with numbers
- It can also work as a more readable Array Formula
What makes this function so especial?
The syntax is: SUMPRODUCT(array1,array2,array3, …) but it can take the form: SUMPRODUCT((criteria column 1=lookup value 1)*(criteria column 2=lookup value 2)* attribute column)You can replace (=) by the following operators: <. <=, >, >=, <>
What does this mean?
The formula will retrieve the cells of the attribute column after all criteria columns are true: SUMPRODUCT(1*1*attribute)
If one condition is false then you would have: SUMPRODUCT(1*0*attribute) and no attribute is computed.
This function will help you to work with Excel Tables that have multiple and complicated criteria.
Now is time to see this function in action. Let’s use the below table…
You can directly calculate the Total cost without inserting neither column F nor formulas =D2*E2 and then =SUM(F2:F22).
Just use this formula: =SUMPRODUCT(D2:D22,E2:E22) that results in $13,947.62.
Conditional Count single (COUNTIF)
How many orders belong to Jones?
=SUMPRODUCT(–(B2:B22=”Jones”)) that results in 5.
If you don’t use (–-) you will receive 0.
What does the double dash (–) mean?
It converts the TRUEs and FALSEs that result after evaluating the column condition to 1s and 0s so they can be considered by SUMPRODUCT.
For example each time B2:B22=”Jones”
B2 = FALSEB3 = TRUE…
Then you convert those TRUE/FALSE to 1/0 so the formula can add them
Conditional Count multiple (COUNTIFS)
How many orders of Pencils belong to Jones?
=SUMPRODUCT(–(B2:B22=”Jones”)*(–(C2:C22=”Pencil”))) that results in 2.
Conditional Sum multiple (SUMIF)
How much did Jones sell?
=SUMPRODUCT((B2:B22=”Jones”)*F2:F22) that results in 5,025.17
Conditional Sum multiple (SUMIFS)
How much Pencils did Jones sell?
=SUMPRODUCT((B2:B22=”Jones”)*(C2:C22=”Pencil”)*F2:F22) that results in 658.58.
Vlookup multiple criteria with numbers
This work when your criteria return one value, for example:
Search the number of units that Jones sold on 7/29/2006, the formula would be this one…
=SUMPRODUCT((A2:A22=DATEVALUE(“7/29/2006″))*(B2:B22=”Jones”)*D2:D22) that results in 21
From all the Excel Functions, SUMPRODUCT is one of my favorite ones. I showed you above why…
Add SUMPRODUCT to your toolbox and you will have one single function that allows you to multiple things.
Moreover, you will have almost the same functionality of Array Formulas without pressing the CTRL + SHIFT + ENTER
7 functions in one make SUMPRODUCT really an Excel Army Swiss knife.