Sumproduct Excel How It Works

SUMPRODUCT function

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.

In this example, we’ll use SUMPRODUCT to return the total sales for a given item and size:

SUMPRODUCT matches all instances of Item Y/Size M and sums them, so for this example 21 plus 41 equals 62.

Syntax

To use the default operation (multiplication):

=SUMPRODUCT(array1, [array2], [array3], . )

The SUMPRODUCT function syntax has the following arguments:

The first array argument whose components you want to multiply and then add.

Array arguments 2 to 255 whose components you want to multiply and then add.

To perform other arithmetic operations

Use SUMPRODUCT as usual, but replace the commas separating the array arguments with the arithmetic operators you want (*, /, +, -). After all the operations are performed, the results are summed as usual.

Note: If you use arithmetic operators, consider enclosing your array arguments in parentheses, and using parentheses to group the array arguments to control the order of arithmetic operations.

Remarks

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error since the ranges aren’t the same size.

SUMPRODUCT treats non-numeric array entries as if they were zeros.

Example 1

To create the formula using our sample list above, type =SUMPRODUCT(C2:C5,D2:D5) and press Enter. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up. The total amount for the groceries is $78.97.

To write a longer formula that gives you the same result, type =C2*D2+C3*D3+C4*D4+C5*D5 and press Enter. After pressing Enter, the result is the same: $78.97. Cell C2 is multiplied by D2, and its result is added to the result of cell C3 times cell D3 and so on.

Example 2

The following example uses SUMPRODUCT to return the total net sales by sales agent, where we have both total sales and expenses by agent. In this case, we’re using an Excel table, which uses structured references instead of standard Excel ranges. Here you’ll see that the Sales, Expenses, and Agent ranges are referenced by name.

The formula is: =SUMPRODUCT(((Table1[Sales])+(Table1[Expenses]))*(Table1[Agent]=B8)), and it returns the sum of all sales and expenses for the agent listed in cell B8.

Example 3

In this example, we want to return the total of a particular item sold by a given region. In this case, how many cherries did the East region sell?

Here, the formula is: =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). It first multiplies the number of occurrences of East by the number of matching occurrences of cherries. Finally, it sums the values of the corresponding rows in the Sales column. To see how Excel calculates this, select the formula cell, then go to Formulas > Evaluate Formula > Evaluate.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Beginners Guide: How to Use the SUMPRODUCT Function

Published date 12th April 2018

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

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.

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.

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

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

In this formula I use adouble negationas 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.

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

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

One thing to note isthe 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 inexample 3of the previous section.

=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 onhow to use SUMPRODUCT with Multiple Criteriafor 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.

SUMPRODUCT with IF

To filter results of SUMPRODUCT with specific criteria, you can apply simple logical expressions directly to arrays in the function,instead of using the IF function. In the example shown, the formulas in H5:H7 are:

where the following named ranges are defined:

If you’d rather avoid named ranges, use the ranges above entered as absolute references. The logical expressions in H6 and H7 can be combined, as explained below.

How this formula works

This example illustrates one of the key strengths of the SUMPRODUCT function – the ability to filter data with basic logical expressions instead of the IF function. Inside SUMPRODUCT, the first array is a logical expression to filter on the color "red":

This results in an array or TRUE FALSE values, which are coerced into ones and zeros with the double negative (–) operation. The result is this array:

Notice the array contains 10 values, one for each row. A one indicates a row where the color is "red" and a zero indicates a row with any other color.

Next, we have two more arrays: one for quantity and one for price. Together with this results from the first array, we have:

Expanding the arrays, we have:

SUMPRODUCT’s core behavior is to multiply, then sum arrays. Since we are working with three arrays, we can visualize the operation as shown in the table below, where the result column is the result of multiplyingarray1*array2*array3:

array1array2array3result
11015150
06180
11415210
09160
011180
010180
1815120
09160
011180
010160

Noticearray1works as a filter – zero values here "zero out" values in rows where the color is not "red". Putting the results back into SUMPRODUCT, we have:

Which returns a final result of 480.

Adding additional criteria

You can extend criteria by adding another logical expression. For example, to find total sales where the color is "Red" and the state is "TX", H6 contains:

Note: SUMPRODUCT is not case-sensitive.

Simplifying with a single array

Excel pros will often simplify the syntax inside SUMPRODUCT a bit by multiplying arrays directly insidearray1like this:

This works because the math operation (multiplication) automatically coerces the TRUE and FALSE values from the first two expressions into ones and zeros.

Excel SUMPRODUCT Function

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.

  • array1- The first array or range to multiply, then add.
  • array2- [optional] The second array or range to multiply, then add.

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 cancounttotal sales for Texas ("TX") with this formula:

And you cansumtotal sales for Texas ("TX") with this formula:

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.

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

and like this after multiplication:

This example expands on the ideas above with more detail.

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:

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.

  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)

What Is the Sumproduct Formula in Excel (and When Should You Use It?)

SUMPRODUCT is a function in Excel that multiplies range of cells or arrays and returns the sum of products. It first multiplies then adds the values of the input arrays. It is a ‘Math/Trig Function’. It can be entered as a part of a formula in a cell of a worksheet. It is a very resourceful function which can be used in many ways depending on the requirement of the user.

Syntax

The syntax of the SUMPRODUCT function is –

=SUMPRODUCT(array1, [array2,….. array_n])

array1– this parameter is the first array or range that will be multiplied then added
array2….array_n– these parameters are the second and onwards arrays or ranges that will be multiplied then added. These are optional parameters.

How does SUMPRODUCT work

Let us look at a very basic example to try and understand how the SUMPRODUCT function works. Suppose we have 2 arrays – <3,5;6,1>and <4,2;7,8>. If we use SUMPRODUCT function on these two arrays, the formula will look like –

The output upon entering this formula in a cell will be 72. Let us understand the maths behind this result. The SUMPRODUCT function calculates the product as follows –

As we can see, the SUMPRODUCT function multiplies and then adds the input arrays. The input arrays can also be written in terms of cell reference. So, if the array <3,5;6,1>is put in cells A3:B4 and <4,2;7,8>is put in D4:E5, then the function will look like –

When we put in this formula in a cell, the result will be the same. The return value of the function will be 72 in this case as well.

Properties of SUMPRODUCT

Having understood the basic working and use of SUMPRODUCT function, let us have a look at some properties of the function. Here are some properties of the SUMPRODUCT function that should be known before using the function:

  • If all the arrays provided as parameters do not have the same number of rows and columns, then the function will return the #VALUE! Error.
  • If the arrays provided as parameters have non-numeric values in them, then these values are treated as 0’s by the SUMPRODUCT function.
  • If only one array is provided, SUMPRODUCT will simply sum the items in the array.
  • Up to 30 arrays can be provided as parameters in the function.

Some Examples of SUMPRODUCT function

Let us look at a few more examples to understand the SUMPRODUCT function better as well as look at the versatility of the function.

Suppose we have a data of cells which contains data in the text format in cells B4:B21 and we want to find out the total number of characters in the data. Then we can use the SUMPRODUCT function to compute the required value. The formula to do so will be –

This formula has another function (LEN) integrated with the SUMPRODUCT function. The formula first calculates the length of all the characters in the data cell wise and then adds them all.

Let us look at another example. Imagine a case where you have a data of inventory of products at a garment shop. The data of the cost of various products such as shirts, jeans, t-shirts etc and their corresponding quantity is available. You have to calculate the average cost of a product in that shop. The price is in A2:A16 and corresponding quantities are in B2:B16. The formula to calculate the required weighted average will be

The resultant value will give the average cost of all the products combined in that shop.

Another way to use SUMPRODUCT is count. To understand this better, let us have a look at another example. Suppose we have the data for the number of subscribers for various outlets in a state and we want to find out the total number of subscribers for a city. We can use the SUMPRODUCT function to calculate the number of subscribers for a city.

Let’s say we have data on retail stores in Maharashtra in A2:A16 and the corresponding number of subscribers registered at the retail store in B2:B16. To compute the total number of subscribers in a city, say Mumbai, we will use the formula –

This formula will give the total number of subscribers in Mumbai. Note that this formula uses the conditional operator “=”. The subscribers of retails in Mumbai only get added up as a result of this conditional operator.

This formula works on the principle of TRUE and FALSE. All the retails in Mumbai become TRUE and all other retails become FALSE under this condition. Then the B2:B16 argument simply calculates the sum of values marked as TRUE, which in this case is for the city Mumbai. Ultimately, we get the total number of subscribers in Mumbai from our data.

Conclusion

We have been introduced to the SUMPRODUCT function in this article. Then we looked at how this function works as well as its versatility. This function can be used in a large number of cases. It handles arrays in a simple manner and is easy to use once you understand how the function works.

Comments: