Multiply the corresponding value and sum.
The Excel SUMPRODUCT function returns a sum of the product of numbers together distributed in multiplies ranges or arrays. SUMPRODUCT is a sophisticated way to add various products.
=SUMPRODUCT(array1, [array2], [array3], …)
array1 – The first range of cells or array to multiply then add together
array2 [optional] – The second range of cells or array to multiply
Sum of products
- The SUMPRODUCT function (sum the product) multiple values positioned in the same place in two or more ranges or array and sum those products together.
- The SUMPRODUCT function accepts the same dimension in each array to perform.
- Treat non-numeric numbers same as if they were zero or a blank cell.
- Up to 255 ranges are allowed, and for meaningful employ, at least 2 arrays are required. In case the user selects only one cell range, the function return sum of chosen numbers just like the SUM function.
Example (Image above)
To multiply and add multiple columns we use SUMPRODUCT function like here we multiply each Quantity with Price and Discount and then add them. Formula =SUMPRODUCT(B7:B10,C7:C10,D7:D10) it return 43.3 (Total Discount).