Excel SUMPRODUCT Function
The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can easily be used inside SUMPRODUCT to extend functionality even further.
- array1 - The first array or range to multiply, then add.
- array2 - [optional] The second array or range to multiply, then add.
The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 ranges or arrays can be supplied.
When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless. But SUMPRODUCT is an amazingly versatile function with many uses. Because it will handle arrays gracefully, you can use it to process ranges of cells in clever, elegant ways.
Arrays and Excel 365
This is a confusing topic, but it must be addressed. The SUMPRODUCT function can be used to create array formulas that don't require control + shift + enter. This is a key reason that SUMPRODUCT has been so widely used to create more advanced formulas. One problem with array formulas is that they usually return incorrect results if they are not entered with control + shift + enter. This means if someone forgets to use CSE when checking or adjusting a formula, the result may suddenly change, even though the actual formula did not change. Using SUMPRODUCT means the formulas will work in any version of Excel without special handling.
In Excel 365, the formula engine handles arrays natively. This means you can often use the SUM function in place of SUMPRODUCT in an array formula with the same result and no need to enter the formula in a special way. However, if the same formula is opened in an earlier version of Excel, it will require control + shift + enter.
The bottom line is that SUMPRODUCT is a safer option if a worksheet will be used in any version of Excel before Excel 365, even if the worksheet was created in Excel 365.
Classic SUMPRODUCT example
The "classic" SUMPRODUCT example illustrates how you can calculate a sum directly without a helper column. For example, in the worksheet below, you can use SUMPRODUCT to get the total of all numbers in column F without using column F at all:
To perform this calculation, SUMPRODUCT uses values in columns D and E directly like this:
=SUMPRODUCT(D5:D14,E5:E14) // returns 1612
The result is the same as summing all values in column F. The formula is evaluated like this:
This use of SUMPRODUCT can be handy, especially when there is no room (or no need) for a helper column with an intermediate calculation. However, the most common use of SUMPRODUCT in the real world is to apply conditional logic in situations that require more flexibility than functions like SUMIFS and COUNTIFS can offer.
SUMPRODUCT for conditional sums and counts
Assume you have some order data in A2:B6, with State in column A, Sales in column B:
Using SUMPRODUCT, you can count total sales for Texas ("TX") with this formula:
And you can sum total sales for Texas ("TX") with this formula:
Note: The double-negative is a common trick used in more advanced Excel formulas to coerce TRUE and FALSE values into 1's and 0's.
For the sum example above, here is a virtual representation of the two arrays as first processed by SUMPRODUCT:
Each array has 5 items. Array1 contains the TRUE / FALSE values that result from the expression A2:A6="TX", and array2 contains the values in B2:B6. Each item array1 will be multiplied by the corresponding item in the array.2 However, in the current state, the result will be zero because the TRUE and FALSE values in array1 will be evaluated as zero. We need the items in array1 to be numeric, and this is where the double-negative is useful.
Double negative (--)
The double negative (--) is one of several ways to coerce TRUE and FALSE values into their numeric equivalents, 1 and 0. One we have 1s and 0s, we can perform various operations on the arrays with Boolean logic. The table below shows the result in array1, based on the formula above, after the double negative (--) has changed the TRUE and FALSE values to 1s and 0s.
Translating the table above into arrays, this is how the formula is evaluated:
SUMPRODUCT then multiples array1 and array2 together, resulting in a single array:
Finally, SUMPRODUCT returns the sum of all values in the array, 275. This example expands on the ideas above with more detail.
You will often see the formula described above written in a different way like this:
=SUMPRODUCT((A2:A6="TX")*B2:B6) // returns 275
Here there is just one array given to SUMPRODUCT. The result is the same, but the syntax is more compact because there is no need to use a double negative (--). This works because the math operation of multiplication (*) automatically coerces the TRUE and FALSE values from (A2:A6="TX") to 1s and 0s. You will see this syntax often in SUMPRODUCT formulas, because it provides a way to use Boolean algebra to create the logic needed in more complex scenarios.
SUMPRODUCT with other functions
SUMPRODUCT can use other functions directly. You might see SUMPRODUCT used with the LEN function to count total characters in a range, or with functions like ISBLANK, ISTEXT, etc. These are not normally array functions, but when they are given a range, they create a "result array". Because SUMPRODUCT is built to work with arrays, it able to perform calculations on the arrays directly. This can be a good way to save space in a worksheet, by eliminating the need for a "helper" column.
For example, assume you have 10 different text values in A1:A10 and you want to count the total characters for all 10 values. You could add a helper column in column B that uses this formula: LEN(A1) to calculate the characters in each cell. Then you could use SUM to add up all 10 numbers. However, using SUMPRODUCT, you can write a formula like this:
When used with a range like A1:A10, LEN will return an array of 10 values. Then SUMPRODUCT will simply sum all values and return the result, with no helper column needed.
See examples below of many other ways to use SUMPRODUCT.
- SUMPRODUCT treats non-numeric items in arrays as zeros.
- Array arguments must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error value.
- Logical tests inside arrays will create TRUE and FALSE values. In most cases, you'll want to coerce these to 1's and 0's.
- SUMPRODUCT can often use the result of other functions directly (see formula examples below)