Introduction to SUMPRODUCT Function
SUMPRODUCT Function returns 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.
Download Sample file
Download Sample file for working with the tutorial.
Syntax of SUMPRODUCT Function
SUMPRODUCT(array1, [array2], [array3], …)
Arguments in SUMPRODUCT Function
- array1: The first array you want to multiply and then add.
- [array2]: The second array you want to multiply and then add.
Notes
- 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 5 cells then cells in array2 should be 5.
- Text and other non-numeric entries will be treated as 0.
Tutorial of SUMPRODUCT Function in MS Excel
In the below example, we have used SUMPRODUCT to multiply and sum up the values from column D and column F. First, it has multiplied the values from column D with column F and then sums up the values.
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 We 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.
Other Math Function in Microsoft Excel
Explore other math function in Microsoft excel. The list of all the math function available for everyday use is given below –
