Exceljet

Quick, clean, and to the point

Excel SUMPRODUCT Function

Excel SUMPRODUCT function
Summary 

The 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
Syntax 
=SUMPRODUCT (array1, [array2], ...)
Arguments 
  • array1 - The first array or range to multiply, then add.
  • array2 - [optional] The second array or range to multiply, then add.
Usage notes 

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:

  A B
1 State Sales
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

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

array1 array2
FALSE 75
FALSE 100
TRUE 125
FALSE 125
TRUE 150

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.

array1   array2   Product
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Sum 275

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)

 

Excel Formula Training

Formulas are the key to getting work done in Excel. In this accelerated video course, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with. See details here.

I sincerely want to register my profound gratitude to Dave and Lisa. There are many features so unique about the tutorials in Exceljet: the simplicity, down-to-earth approach and the connection between the instructor and the learner. -John
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course