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:

 ABCD
1X1X2X3 X4
215913
3261014
4371115
5481216

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.

Leave a Reply

Your email address will not be published. Required fields are marked *