SUMPRODUCT Function

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. 

Worksheet shown

In the worksheet shown above, SUMPRODUCT is used to calculate a conditional sum in three separate formulas:

I5=SUMPRODUCT(--(C5:C14="red"),F5:F14) //
I6=SUMPRODUCT(--(B5:B14="tx"),--(C5:C14="red"),F5:F14)
I7=SUMPRODUCT(--(B5:B14="co"),--(C5:C14="blue"),F5:F14)

The results are visible in cells I5, I6, and I7. The article below explains how SUMPRODUCT can be used to calculate these kinds of conditional sums, and the purpose of the double negative (--).

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:

=SUMPRODUCT(D5:D14,E5:E14)
=SUMPRODUCT({10;6;14;9;11;10;8;9;11;10},{15;18;15;16;18;18;15;16;18;16})
=SUMPRODUCT({150;108;210;144;198;180;120;144;198;160})
=1612

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:

=SUMPRODUCT(--(A2:A6="TX"))

And you can sum total sales for Texas ("TX") with this formula:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

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. Once 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({0,0,1,0,1},{75,100,125,125,150}) 

SUMPRODUCT then multiples array1 and array2 together, resulting in a single array:

=SUMPRODUCT({0,0,125,0,150})

Finally, SUMPRODUCT returns the sum of all values in the array, 275. This example expands on the ideas above with more detail.

Abbreviated syntax in array1

You will often see the formula described above written in a different way like this:

=SUMPRODUCT((A2:A6="TX")*B2:B6) // returns 275

Notice all calculations have been moved into array1. The result is the same, but this syntax provides several advantages. First, the formula is more compact, especially as the logic becomes more complex. This is because the double negative (--) is no longer needed to convert TRUE and FALSE values — the math operation of multiplication (*) automatically converts the TRUE and FALSE values from (A2:A6="TX") to 1s and 0s. But the most important advantage is flexibility. When using separate arguments, the operation is always multiplication, since SUMPRODUCT returns the sum of products. This limits the formula to AND logic since multiplication corresponds to addition in Boolean algebra. Moving calculations into one argument means you can use addition (+) for OR logic, in any combination. In other words, you can choose your own math operations, which ultimately dictate the logic of the formula. See example here.

With the above advantages in mind, there is one disadvantage to the abbreviated syntax. SUMPRODUCT is programmed to ignore the errors that result from multiplying text values in arrays given as separate arguments. This can be handy in certain situations. With the abbreviated syntax, this advantage goes away, since the multiplication happens inside a single array argument. In this case, the normal behavior applies: text values will create #VALUE! errors.

Note: Technically, moving calculations into array1 creates an "array operation" and SUMPRODUCT is one of only a few functions that can handle an array operation natively without Control + Shift + Enter in Legacy Excel. See Why SUMPRODUCT? for more details.

Ignoring empty cells

To ignore empty cells with SUMPRODUCT, you can use an expression like range<>"". In the example below, the formulas in F5 and F6 both ignore cells in column C that do not contain a value:

=SUMPRODUCT(--(C5:C15<>"")) // count
=SUMPRODUCT(--(C5:C15<>"")*D5:D15) // sum

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

=SUMPRODUCT(LEN(A1:A10))

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.

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 still require control + shift + enter. The bottom line is that SUMPRODUCT is a safer option if a worksheet will be used in older versions of Excel. For more details and examples, see Why SUMPRODUCT? 

Notes

• 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)