Sumproduct

The Excel SUMPRODUCT function multiplies the values in the given cell ranges or arrays and then sums the results.

So the first value of the first array is multiplied with the first value of the second array. Then, the second values of the arrays are multiplied, etc. Finally, all multiplication results are summed. The resulting value is returned.

The Sumproduct function works with arrays without the need for special syntax. That’s why it is often used in advanced formulas. See example 2 below for such a formula.

Syntax

=SUMPRODUCT(Array 1, [Array 2])

Example 1: A simple Excel Sumprduct formula

The simplest example of a Sumproduct formula can look something like this:

=SUMPRODUCT(A2:A4, B2:B4)

Explanation

The resulting 21 is calculated as follows: (4 x 2.5) + (2 x 3) + (1 x 5) = 21. So the quantity values in Array 1 are multiplied with the cost values in Array 2. The resulting values are summed and returned.

Example 2: Count

In the above example, we’ve been using Sumproduct like how you would expect a function that’s called Sumproduct to be used. It summed the products of the given arrays. However, we can do more with Sumproduct. Things that may not be as intuitive, like counting. Let’s take a look at what that looks like.

Explanation

What is going on here? You might be asking. How did we use Excel’s Sumproduct as a COUNTIF function? What is “–“? And where’s Array 2?

Single argument

Let’s go through this step-by-step. First, what you need to know is that if you use Sumproduct with a single argument, it will just sum the numbers in that cell range/array. No multiplication is done at all. That’s also the case here. So forget about the Product in Sumproduct for a second and focus on the Sum.

Spilling

Then, the (A2:A8 = “BMW”) part. That’s easiest to understand by looking at the image below. The image shows what happens when you create a formula with just the (A2:A8 = “BMW”) part:

This is called spilling: the results spill over into the cells below the formula. This is Excel’s way of returning multiple values. As you can see, the resulting TRUE and FALSE values are TRUE when the condition = “BMW” is true, so when the cell contains the text BMW and FALSE otherwise.

This is also what happens in the example. The (A2:A8 = “BMW”) part given to Sumproduct is converted into an array of TRUE’s and FALSE’s. Internally it looks like this:

=SUMPRODUCT(--{FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE})

Double negation

Finally, the last piece to understanding this example, the double negation –. The double negation converts TRUE’s and FALSE’s to 1’s and 0’s. True becomes 1 and False becomes 0. Why it does this is for another time. For now, it’s only important that you realize what the result will be:

=SUMPRODUCT({0, 1, 0, 0, 1, 0, 0})

And now it becomes clear how this Sumproduct function was able to count the number of BMW’s in a cell range. It was able to do so because the cell range was being transformed into an array of 1’s and 0’s that the Sumproduct function could then sum up. The resulting 2 comes from adding all values: 0+1+0+0+1+0+0 = 2.

In this example, we used a condition (=”BMW”) to get our array of TRUE’s and FALSE’s. But you can use functions to create these arrays as well. Functions that return TRUE or FALSE like ISBLANK, ISERROR or ISTEXT. And also functions that return numbers, like LEN.

Questions

Drawbacks

The Excel Sumproduct function has some drawbacks:

1. Sumproduct is complicated. It can be counter-intuitive to see a function called Sumproduct do counting. And the function is often combined with the (admittedly unnecessarily complicated) double negation (–). Something that also does not help with understanding. Don’t get me wrong, it is a really powerful function, but when you’re just learning about it, it can take some time to wrap your head around this function.