Beginners Guide: How to Use the SUMPRODUCT Function

Page Image - Beginner Guide How to Use the SUMPRODUCT Function


SUMPRODUCT is a multi-purpose formula. In essence, it multiplies arrays and returns the sum of those products. It is different from most Array formulas in Excel in that it doesn’t need CTRL + SHIFT + ENTER to work. SUMPRODUCT proves valuable in many scenario’s. You can use it for COUNT, as INDEX MATCH or replicate a SUMIF(S). And not only that, it’s actually a much more powerful formula with many uses. As it has so many uses, I will spend several posts on this formula.

Video Tutorial

If you would rather read the tutorial or need more instructions, then continue reading.

How does SUMPRODUCT work?

The generic formula is:

=SUMPRODUCT( array1, array2, array3, …)

The input for this formula is 1 or several arrays. An array is nothing else then a selection of cells. The formula requires the user to define at least 1 and at most 255 arrays. The formula multiplies each of the components in the arrays. Next it returns the sum of each of those multiplications. This may still sound like magic to you now, but follow along and you will discover how the ‘magic’ works.

Using SUMPRODUCT as SUM Formula

Below example uses the formula:

=SUMPRODUCT( (C6:C11) )

The defined array is cells C6:C11 (note that I defined only 1 array in this example). This is what happens

  • First, the formula checks the given amount of arrays. In case of multiple arrays, the formula multiplies the components of each of the arrays.
  • Second, the formula sums up the results of the previous action.

As this example contains a single array, no multiplication takes place. Summing up the results then amounts to ‘3792’. You could say that defining 1 array in a SUMPRODUCT formula, is equal to a SUM formula of the selected range. I do not recommend replacing all your SUM formulas with SUMPRODUCT. Yet, for educational purposes this is good to know. In the next section the focus will be on multiplying several arrays within your formula.

 Multiplying Arrays

An important skill to learn when using SUMPRODUCT, is to add criteria. For that we need to be comfortable with multiplying arrays. Let’s therefore stand still by what it means to multiply two arrays.

Below are 4 examples of what happens when you multiply arrays. All examples have the same structure. You find two arrays, named ‘Array 1’ and ‘Array 2’. The SUMPRODUCT formula multiplies the components of these arrays with each other. And then returns the sum of each of those multiplications.

Note: The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

For checking purposes I have added a manual method. The ‘Multiply Arrays’ column multiplies the values of ‘Array 1’ by ‘Array 2’. A basic SUM formula then sums up all the multiplied values. The manual method should be similar to the SUMPRODUCT method. However, sometimes it’s not.

In example 1 the formula to multiply the components of ‘Array 1’ by ‘Array 2’ is:

=SUMPRODUCT( B4:B9, C4:C9)

The calculation behind this is 283*0 + 539*0 + 921*0 + 722*0 + 833*0 + 494*0. The answer to that is ‘0’. In example 2 that would be 283*5 + 539*9 + 921*18 + 722*40 + 833*11 + 494*4 = ’62,863’. So far all works as expected.

Example 3 is a special case. The answer with the SUMPRODUCT formula, is different from the manual method. Why is that? SUMPRODUCT treats array entries that are not numeric as if they were zeros. When multiplying the numbers of ‘Array 1’ with the Boolean values of ‘Array 2’, the formula actually multiplies all the numbers by zero. The answer is ‘0’ for each of the multiplied components. SUMPRODUCT then sums up all the values, and gets to a total of zero.

So why does the manual SUM method work? When Excel performs a numerical operation with a Boolean value, it transforms TRUE to 1 and FALSE to 0. That’s why the manual multiplication in the ‘Multiply Arrays’ column results in a number. Yet SUMPRODUCT does not treat the multiplication of the Arrays in the same way.

The solution to make the formula work, is to do a numerical operation with ‘Array 2’ before multiplying it with ‘Array 1’. The following formula would give the right result:

=SUMPRODUCT( B15:B20, – – C15:C20)

In this formula I use a double negation as numerical operation. The values are first turned negative, and then turned positive again. This forces the Boolean values to transform to numbers with as result the situation in example 4. And SUMPRODUCT does handle that situation gracefully.

Tip: Up to now we have separated arrays with a list separator. An alternative is to write multiple arrays within the same array argument. You can also write the formula in example 3 as:

=SUMPRODUCT( (B15:B20) * (C15:C20) )

Notice that each array, now has its own place within parenthesis. Writing multiple arrays within a single array argument has an advantage. The main advantage is that Boolean values are automatically converted to numbers. So no chance of forgetting that. From now on, I will continue writing SUMPRODUCT formulas using this method.

If you would like to take a closer look at these examples, you can find them in the below file:

SUMPRODUCT – basic examples

Using SUMPRODUCT as SUMIF Formula

The SUMPRODUCT formula allows you to add criteria and add up the components that meet these criteria. In other words, it can replicate a SUMIF formula.

Below example uses the formula:

=SUMPRODUCT( (C7:C12) * (B7:B12 = “Berry”))

One thing to note is the brackets. When adding criteria to SUMPRODUCT, make sure the argument including the EQUAL sign is within brackets.

If we take the part “= Berry” away, the formula looks just like earlier examples. Adding “= Berry” to the array containing names, tests each component for being equal to ‘Berry’. For the SUMPRODUCT formula, the multiplication then looks like the table in Column E and F below.

When multiplying the Arrays as in above picture, the Boolean values transform to 1’s and 0’s, resulting in a total of ‘283’. Just like in example 3 of the previous section.

Tip: To make the formula dependent on a cell, replace the name by a cell reference:

=SUMPRODUCT( (C4:C9) * (B4:B9 = I4) )

And that’s how SUMPRODUCT can replace a SUMIF formula. Yet there’s many more things you can do. For example, creating a multidimensional formula that adds up both rows and columns. Or combining multiple AND and OR conditions, or use the SUMPRODUCT formula instead of COUNTIFS. In the next post I focus on how to use SUMPRODUCT with Multiple Criteria for more advanced calculations.

I hope this clarifies the basics of the SUMPRODUCT formula. Please leave a comment below with questions or suggestions.

I hope this clarifies the basics of the SUMPRODUCT formula. Please leave a comment below with questions or suggestions.

Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills. Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

9 Responses

    • Hi Sandeep. And thanks for commenting. You’re right! In the example provided, a single parenthesis definitely works. So please feel free to use that. In the making of the screenshots I may have edited some more complex ones to a basic one and left the parenthesis.

      /Rick

  1. Rick, in the example under the head “Multiplying Arrays”, you have shown semi-colon between the ranges in the formula bar but in the formula at the foot of the table, you have shown comma. Is it okay?

    • Hi Sandeep,

      Doh, you have a sharp eye! Depending on your computer’s settings you will use a semicolon or a comma as a separator. In my daily use I have a semi-column as separator. For the blog however I try to use a ‘comma’ as separator, as many computers have that as a standard. The picture and the formula bar are not consistent, thanks for spotting that. Please use the appropriate separator for your computer. The one that you always use when writing formulas.

    • Bill. Thanks for opening my eyes, you’re absolutely right. I didn’t realise criterium was different than criterion. I got confused by the Dutch way of saying it (criterium). Great to see you around.

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment