​​​​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])

Argument  Argument description
1Array 1The cell range or array to multiply with Array 2 and then sum. If no Array 2 is given, Array 1 will just be summed.
2Array 2 (Optional)Another cell range or array to multiply with Array 1 and then sum.
3 … 255Array 3 … 255 (Optional)Optionally, more cell ranges or arrays to multiply with the others before summing.

Example 1: A simple Excel Sumprduct formula

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

=SUMPRODUCT(A2:A4, B2:B4)
Simple SUMPRODUCT example. Based on two cell ranges, A2:A4 (Quantity) and B2:B4 (Cost), the Sumproduct function multiplies the values and sums the result. Conveniently calculating the total cost.
Simple SUMPRODUCT example. Based on two cell ranges, A2:A4 (Quantity) and B2:B4 (Cost), the Sumproduct function multiplies the values and sums the result. Conveniently calculating the total cost.

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.

Counting how often the value BMW occurs in the range A2:A8. The result is 2 times.
Counting how often the value BMW occurs in the range A2:A8. The result is 2 times.

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:

Result of =(A2:A8 = "BMW"). Excel returns multiple results in the cells below the formula.
Result of =(A2:A8 = “BMW”). Excel returns multiple results in the cells below the formula.

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

What does Sumproduct do with texts in input arrays?

All texts in arrays are treated as if they are the number 0. So if you don’t convert arrays with TRUE’s and FALSE’s to 0’s and 1’s, the result will always be 0.

What happens when I use different sized input arrays?

A #VALUE! error will be returned if any input array has a different number of elements than any other.

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.
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>