Excel SUMPRODUCT Function

The SUMPRODUCT function works with arrays, but it doesn’t require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 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 and without complaint, you can use it to process ranges of cells in clever, elegant ways (see links to formula examples on this page).

To illustrate how SUMPRODUCT works, here are a few common examples.

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 to Texas (“TX”) with the this formula:

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

Note: Don’t be confused by the double-negative. This is a common trick used in more advanced Excel formulas to coerce TRUE and FALSE values into 1’s and 0’s. See more below…

For the sum example above, here is a virtual representation of the two arrays as first processed by SUMPRODUCT:

Each array has 5 items. The first array contains the TRUE / FALSE values that result from the expression A2:A6=”TX”, and the second array is the contents of B2:B6.   Each item in the first array will be multiplied by the corresponding item in the second array. However,  in the current state, the result of SUMPRODUCT will be zero because the TRUE and FALSE values will be treated as zero.  We need the items in array1 to be numeric — they need to be “coerced” into ones and zeros. This is where the double-negative comes in.

By using the double negative — (double unary, for you technical types) we are able to coerce the TRUE/FALSE into the numeric values one and zero, as shown in the virtual representation below. The last column “Product” represents the result of multiplying the two arrays together. The summed result, 275, is the value that SUMPRODUCT returns.

Using the curly brace syntax for arrays, the example looks like this after coercion:

=SUMPRODUCT({0,0,1,0,1},{75,100,125,125,150}) 

and like this after multiplication:

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

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 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 many other ways to use SUMPRODUCT.

Notes:

1. SUMPRODUCT treats non-numeric items in arrays as zeros.
2. Array arguments must must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error value.
3. 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.
4. SUMPRODUCT can often use the result of other functions directly (see formula examples below)