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