What is SUMPRODUCT Function?
The SUMPRODUCT function multiplies corresponding ranges or arrays and returns the sum of those products.
SUMPRODUCT Syntax
SUMPRODUCT(array1, [array2], [array3], ...)
array1, required. The first data to be multiplied, then sum.
[array2], optional. The second data to be multiplied, then sum.Usage notes:
- array argument must have the same dimensions, otherwise it will generate error #VALUE!.
- Any non-numeric value will be considered as number 0
How to Use SUMPRODUCT Function in Excel
Suppose there is data like the picture below. How is the result of the SUMPRODUCT function in column F?
SUMPRODUCT Function #1
=SUMPRODUCT(B2:B7)
There is only 1 array. If the SUMPRODUCT function uses only its first array, then the result is the same as the SUM function result.
The result is 21.
SUMPRODUCT Function #2
=SUMPRODUCT(B2:B7,C2:C7)
The SUMPRODUCT function use 2 arrays. Excel will multiply the number in the first array with the number in the second array then add it up.
=(1*1)+(2*1)+(3*2)+(4*3)+(5*5)+(6*8)
The result is 94.
SUMPRODUCT Function #3
=SUMPRODUCT(B2:B7,C2:C6)
Similar to the SUMPRODUCT function #2, but the first and second array dimensions are not the same. The first array has 7 numbers while the second array has only 6 numbers. Array dimensional differences generate error #VALUE!.
SUMPRODUCT Function #4
=SUMPRODUCT(A2:A7,B2:B7,C2:C7)
The SUMPRODUCT function use 3 arrays, all have the same dimensions, but the first array contains text. All data other than numbers will be considered 0. All numbers multiplied by the number 0 result is 0.
You can see the complete results of all SUMPRODUCT functions in the picture below.
SUMPRODUCT Examples
There is a purchase transaction as shown below.
What is the price of all smartphone purchases above? Which excel function will be used?
The easiest way is using the helper column, then sum it using the Excel SUM function, but the Excel SUMPRODUCT function can answer questions without a helper column.
The Formula
=SUMPRODUCT(C2:C5,D2:D5)
The Result
Simple, fast, accurate and no helper column 🙂
For solutions using SUM function and a helper column, please read the article below.
