SUMPRODUCT function in Excel – Calculate Sum of Products in Excel

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

SUMPRODUCT( array1, [array2], [array3], … )          

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

SUMPRODUCT Function in Excel 1

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

SUMPRODUCT Function in Excel 2

 

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

SUMPRODUCT Function in Excel 3

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

SUMPRODUCT Function in Excel 4

NOTE :  #VALUE! Error occurs if the input arrays have different dimension

previous SUMPRODUCT function in Excel                                                                                                                next SUMPRODUCT function in Excel