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.

Purpose 

Multiply, then sum arrays

Return value 

The result of multipled and summed arrays

Arguments 

  • array1 – The first array or range to multiply, then add.
  • array2 – [optional] The second array or range to multiply, then add.

Syntax 

=SUMPRODUCT(array1, [array2], …)

Usage notes 

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 call this kinds kind of conditional sum, 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:

 AB
1StateSales
2UT75
3CO100
4TX125
5CO125
6TX150

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:

array1array2
FALSE75
FALSE100
TRUE125
FALSE125
TRUE150

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.

array1 array2 Product
0*75=0
0*100=0
1*125=125
0*125=0
1*150=150
Sum275

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 examples  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

Norice 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 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.

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 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.

0
Would love your thoughts, please comment.x
()
x