## 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

#### SUMPRODUCT with array arguments

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 this:

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

Here, each has four entries.

The above formula returns 11874.

Now, let's see how **SUMPRODUCT **calculates and returns 11874.

The **SUMPRODUCT** function calculates the product 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.

#### SUMPRODUCT with range arguments

In this example, you'll learn how to use the **SUMPRODUCT** function with range arguments.

Suppose you have the following demo 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 |

You can use **SUMPRODUCT**() with range arguments, as the following example shows:

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

As you can see, there are four ranges of cells, 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

Let's take a look at another example below:

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

**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

#### Array arguments with different dimensions

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

Here is an example:

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

The formula returns #VALUE! (error value) because the first three arrays have four entries, and the last one has only three entries.

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.