SUMPRODUCT

Multiply, then sum arrays

Sponsored Link

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:

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

array1array2
FALSE75
FALSE100
TRUE125
FALSE125
TRUE150

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
Sum275

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)

Sponsored Link

0 votes. 0 / 5