# Excel SUMPRODUCT Function and Formula with Examples

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

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.