SUMPRODUCT Function in Excel calculates and returns the sum of the products of Arrays that has been passed as input.
Syntax of SUMPRODUCT function in Excel
Where array is numeric array for which we have to find the sum of products.
- All the input arrays must have same dimension
- Array can be passed as cell reference or as values directly. Arrays that are supplied directly should be surrounded by curly braces.
Example of SUMPRODUCT function in Excel
Formula
Example calculates
- Sum of products of Array 1 and Array 2 passed as cell reference
- Sum of products of Array 1, Array 2 and a numeric array in which Array 1 and Array 2 are passed as cell reference and a numeric array is passed directly
So the result will be like
Result
Example of Sum of products of arrays with condition using SUMPRODUCT function in Excel
With SUMPRODUCT Function we can also calculate the sum of product of arrays based on some conditions as well. Lets see an example below
Formula
In the above example we will calculate the sum of products of Array 1 and Array 2 only if Array 1 value is equal to 2.
This can be accomplished by using double negative followed by condition in parenthesis followed by arrays
=SUMPRODUCT(–(A2:A6=2),A2:A6,B2:B6)
So the result will be
Result
NOTE : #VALUE! Error occurs if the input arrays have different dimension
