# 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

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

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.