## How to Use the SUMPRODUCT Function and Formula in Excel

In this tutorial, you'll learn how to use the **SUMPRODUCT** function and formula in Excel. **SUMPRODUCT** multiplies arrays or ranges and returns the sum of products.

### SUMPRODUCT Syntax

The syntax of the **SUMPRODUCT** function is as follows:

=SUMPRODUCT(array1, [array2], [array3],...)For example:

=SUMPRODUCT({1,2,3},{10,20,30})

=SUMPRODUCT(A1:B4,D2:E4)

### Return value

The sum of products

### Arguments

**Array1**: The first array or range that you want to multiply then add.**Array2**, array3, ...: Other arrays or ranges that you want to multiply then add.

**Remarks**

- You can specify the array arguments up to 255.
- The array arguments must have the same dimensions.

### Excel SUMPRODUCT Function Examples

#### Example 1: SUMPRODUCT with Array Arguments

In this example, I'll show you how **SUMPRODUCT** works.

Suppose you have four arrays - **{1,2,3,4}**, **{5,6,7,8}**, **{9,10,11,12}** and **{13,14,15,16}**. If you use the **SUMPRODUCT** function on these four arrays, the formula will look like -

=SUMPRODUCT({1,2,3,4},{5,6,7,8},{9,10,11,12},{13,14,15,16})

Here, there are four arrays, and each has four entries. If you enter the above formula in a cell, the return value will be 11874. Now let's see how **SUMPRODUCT **calculates and returns 11874.

The **SUMPRODUCT** function calculates as follows -

=(1 * 5 * 9 * 13) + (2 * 6 * 10 * 14) + (3 * 7 * 11 * 15) + (4 * 8 * 12 * 16)

=(585) + (1680) + (3465) + (6144)

=11874

As you can see, **SUMPRODUCT**() multiplies and then adds the corresponding entries of the arrays given in the above formula.

#### Example 2: SUMPRODUCT with Range Arguments

In the previous example, you've learned to use the **Excel SUMPROUDCT **function with the array arguments. In this example, you'll learn how to use the **SUMPRODUCT** function with the range arguments.

Suppose you have the following sample data:

A | B | C | D | |

1 | X1 | X2 | X3 | X4 |

2 | 1 | 5 | 9 | 13 |

3 | 2 | 6 | 10 | 14 |

4 | 3 | 7 | 11 | 15 |

5 | 4 | 8 | 12 | 16 |

Now let's take a look at the following **SUMPRODUCT** formula:

=SUMPRODUCT(A2:A5,B2:B5,C2:C5,D2:D5)

Here, as you can see, there are four ranges of cells in this formula, and each is a reference to an array of four entries.

**SUMPRODUCT**() calculates the product as follows:

=**(A2 * B2 * C2 * D2)** + **(A3 * B3 * C3 * D3)** + **(A4 * B4 * C4 * D4)** + **(A5 * B5 * C5 * D5)**

= (1 * 5 * 9 * 13) + (2 * 6 * 10 * 14) + (3 * 7 * 11 * 15) + (4 * 8 * 12 * 16)

=11874

Next, let's take a look at another formula:

=SUMPRODUCT(A2:D2,A3:D3,A4:D4,A5:D5)

Here, each argument is a reference to an array of four entries. **SUMPRODUCT** calculates the product as follows:

**=(A2 * A3 * A4 * A5 )** + **(B2 * B3 * B4 * B5)** + **(C2 * C3 * C4 * C5)** + **(D2 * D3 * D4 * D5)**

=(1 * 2 * 3 * 4) + (5 * 6 * 7 * 8) + (9 * 10 * 11 * 12) + (13 * 14 * 15 * 16)

=57264

#### Example 3: Array Arguments with Different Dimensions

The array arguments must have the same dimensions. Otherwise, **SUMPRODUCT**() returns the error value #VALUE!.

Let's take a look at an example:

=SUMPRODUCT({1,2,3,4},{5,6,7,8},{9,10,11,12},{13,14,15})

Here, as you can see, there are four array arguments, but the last one contains only three entries, so **SUMPRODUCT**() returns #VALUE!, which is the error value.

In this tutorial, you've learned how to use the **SUMPRODUCT** function in Excel. **SUMPRODUCT()** multiplies arrays or ranges and returns the sum of products.