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:

 ABCD
1X1X2X3 X4
215913
3261014
4371115
5481216

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.

Leave a Reply

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