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.