SUMPRODUCT Function can be used to return a value after sum and multiplies values from the ranges or arrays. In simple words, it first multiplies the corresponding cells from ranges and then sums up all the values. It’s one of the most useful and powerful functions in Excel that can be used for data analysis.
Syntax
SUMPRODUCT(array1, [array2], [array3], …)
Arguments
- array1: The first array you want to multiply and then add.
- [array2]: The second array you want to multiply and then add.
Example
In the below example, we have used SUMPRODUCT to multiply and sum up the values from column D and column F.
Here we have specified the range A1:A8 in the first array and in the second array we have specified the range C1:C8.
Now what happens is its multiple values from both ranges with each other (A1xC1, A2xC2, and so on…).
And after multiplying all the values, it sums them up and shows you that value in the result.
As the name suggests SUMPRODUCT function, first calculates the product of all the values and then sums all those to get it in a single cell.
To make you understand how powerful SUMPRODUCT is, in the following example, we have achieved the same result with a helper column and the SUM function.
That’s why I said, SUMPRODUCT is one of the most powerful functions that we have in Excel to analyze data and perform complex calculations. And going one step ahead we have used the SUMPRODUCT to lookup for a value from a table.
Important Points
- If skip you to specify array2, SUMPRODUCT will simply sum the array1.
- The maximum size of each array should be the same. If array1 has five cells, then cells in array2 should be 5.
- Text and other non-numeric entries will be treated as 0.
⇠ Back to Excel Functions