Definition: The Excel SUMPRODUCT functions multiplies ranges together and returns the sum of products. The SUMPRODUCT function is widely used to do advanced count and sum calculations where COUNTIFS and SUMIFS don’t work.
When doing counts and sums, additional functions can be included to help match more specific criteria. SUMPRODUCT evaluates each range separately before multiplying the resulting ranges, making the additional criteria easy to manage.
SUMPRODUCT takes a range of cells as its input. These ranges are often referred to as arrays.
While SUMPRODUCT was intended to multiply then sum ranges, it’s commonly used to do the following:
You might see a few different ways that SUMPRODUCT is written. For example, some formulas might contain commas (,) between the ranges/arrays, and some may include an asterisk (*) . The section below will touch on the syntax differences, but the formula below is how the Excel formula bar will look.
=SUMPRODUCT (array1, [array2], [array3])
array1 | The first array or range |
[array2] | Optional – the second array or range |
[array3] | Optional – the third array or range. When using SUMPRODUCT to do a count or sum, generally, the last argument should be your values; the preceding ranges would be your search criteria, which will help increase readability. Ultimately the order of ranges does not matter. |
Various SUMPRODUCT formulas use a double negative (–) in front of a range with criteria. This converts TRUE to 1 and FALSE to 0. When additional ranges are added to the formula, these 1’s and 0’s will be multiplied by the next ranges.
You will also see various SUMPRODUCT formulas use an asterisk or a comma to separate the different ranges/criteria.
An asterisk (*) between ranges will force arithmetic operations to be performed between the ranges. Once all operations are performed, the values are summed are normal. When doing arithmetic operations, TRUE and FALSE are automatically converted to 1 and 0, so the double negative is not needed.
A comma (,) will not perform any arithmetic operations between ranges. The ranges will be multiplied and then summed. If you multiply a number by TRUE or FALSE with this syntax, the result will return 0. The double negative is needed when using the comma (,) syntax.
We recommend always using the asterisk syntax and grouping each criteria/range inside of their own parenthesis. This will make your SUMPRODUCT easier to read and will avoid any confusion with the double negative operators.
Technically, using this method, you will only have one argument, array1. However, this array1 will contain all of your other ranges and criteria.
The standard use of SUMPRODUCT can multiply the sum of two columns without a helper column. In the above example, you could get the total cost of all items without the need for Column F.
Below is an example of the standard SUMPRODUCT formula. If you click “Show Steps” you will see how Excel will evaluate each step. This “Show Steps,” button is also included in other examples.
Show Steps +
=SUMPRODUCT({8,4,11,9,7,5,2,3,6,10},{14,17,12,18,19,21,11,10,16,18}) //ranges converted to arrays { } =1040 //numbers in step 3 summarized
If we had a sales data report that listed the location, number of customers, and total sales for each rep, we could use SUMPRODUCT to calculate various counts and sums.
Here is an example of counting the total number of TX sales reps. This formula uses a double negative sign in front of the criteria. This is because there are no other ranges and no ability to perform arithmetic operations. This is one of the few times the double negative is needed, but a simple COUNTIFS could be used here.
Show Steps +
=SUMPRODUCT(--{TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE}) //ranges converted to arrays { } and looks for the TX match =3 //numbers in step 3 summarized
You could count the number of sales reps in TX that handle Auto using the formula below.
=SUMPRODUCT((C5:C14="tx")*(D5:D14="auto"))
In the above sample data for sales reps, we could use SUMPRODUCT to summarize the number of customers in TX. The formula would look like the one below.
A sum with one criteria is a good example to explain further. Just like in the examples above, ranges are converted to arrays. Arrays will contain TRUE or FALSE when criteria are applied, and then finally, the arrays are multiplied and then summed.
The last array has no criteria, so the values will remain the same even as other arrays convert to TRUE / FALSE.
Show Steps +
=SUMPRODUCT({TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE}*{35,32,50,48,7,17,20,18,39,44}) //ranges converted to arrays { }. TX matches are converted to TRUE =SUMPRODUCT({35,0,0,0,7,0,20,0,0,0}) //the asterisks means arithmetic operations will be performed. Meaning TRUE * 35 is the same thing as 1 * 35. =62 //numbers in step 3 summarized
Sticking with the sales example, if we wanted to sum up the number of Auto customers in TX, we could use this formula.
=SUMPRODUCT((C5:C14="tx")*(D5:D14="auto")*(E5:E14))
You could use an OR criteria for both a sum and a count. The only difference between sum and count would be including an extra range of values to summarize.
The key with OR criteria is putting all of the rules in their own brackets and then using the addition sign (+).
What happens here is that the arrays are added together. Remember that when arithmetic operations are performed on arrays, TRUE and FALSE become 1 and 0. So if you add TRUE and FALSE, the result becomes 1. For the sake of clarity, we won’t show all steps, but you can use the “Evaluate Formula” button in Excel to look at each step.
Below is the formula needed to count all TX or UT customers.
=SUMPRODUCT(((C5:C14="tx")+(C5:C14="ut"))*(E5:E14))
If you need to OR criteria using different columns, this can get tricky. Remember that the arrays will be added together using arithmetic. If you have two columns that meet both criteria, then you would get TRUE TRUE. Added together, that would result in 2.
For example, if you want to count the number of TX or Auto customers and use the standard, you would get TRUE for columns C and D, row 5. That would result in the number 2 for the criteria rage. That number 2 would then be multiplied by the coatomer count, 35. The result would be 70 and double what it should be!
In this situation, you can use a simple Excel function called SIGN. The SIGN function will return 1 if a number is positive, 0 if 0, and -1 if negative. So, in this example, applying SIGN to the array would transform 2 into a 1.
Below is an example of this formula. You can click “Show Steps” to visualize how Excel will evaluate each step, namely the SIGN function.
Show Steps +
=SUMPRODUCT(SIGN(({TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE}+{TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE}))*(E5:E14)) //criteria are evaluated =SUMPRODUCT(({1,0,0,0,1,1,1,0,1,0}*{35,32,50,48,7,17,20,18,39,44}) //now just two arrays to multiply then sum = 118 //the result
Because of the flexibility SUMPRODUCT offers in evaluating arrays, there are a lot of advanced criteria you can search for. Here are some of the most common examples. These examples are based on based sales data by month. These examples are also provided in the sample workbook provided.
With a SUMIFS formula, summing by year would require you to create dates and create multiple criteria for greater than the beginning of the year and less than the end of the year. With SUMPRODUCT this can be accomplished with one simple formula.
=SUMPRODUCT((YEAR(D5:D14)=2022)*(E5:E14))
SUMPRODCUT makes it simple to even summarize by the short month name, for example, “Jan”.
=SUMPRODUCT((TEXT(D5:D14,"MMM")="Jan")*(E5:E14))
SUMPRODUCT is a great way to do a case-sensitive sum. This case-sensitive sum could also be combined with other criteria.
The key to a SUMPRODUCT for anything case sensitive, is using the EXACT function. When a range of cells is compared to a single string with EXACT, the function will compare if the arrays are the same and return an array. This will evaluate in an array full of TRUE and FALSE.
=SUMPRODUCT((EXACT("Allen",B5:B14))*(E5:E14))
Sometimes your information system will give data that contains text in place of numbers. There are a few ways to ignore text inside of a SUMPRODUCT.
The easiest method is using IFERROR inside of the values to force all text to be 0. The other method involves using ISNUMBER along with the double negative sign to convert text to 0 and numbers to 1; then, you would include the range of values as an additional array to then multiply the values.
We recommend using the IFERROR method to ignore text values. It is much easier to troubleshoot and will provide shooter formulas. Below are two examples of SUMPRODUICT ignoring the text.
=SUMPRODUCT((C5:C14="tx")*(IFERROR(F5:F14+0,0))) =SUMPRODUCT((C5:C14="tx")*(--ISNUMBER(F5:F14)),F5:F14)
Sometimes you need a count or a sum that looks at text that might contain a substring. The trick here is to use the ISNUMBER and search function together.
Here is an example if toy wanted sum the sales for reps that contain the string “en”.
=SUMPRODUCT((ISNUMBER(SEARCH("en",B5:B14)))*(E5:E14))
The key to using the beginning or ending of a string as a criteria is using the LEFT and RIGHT functions. You can specify the numbers of charterers to search for. Here is an example to sum sales for reps that begin with “b”.
=SUMPRODUCT((LEFT(B5:B14,1)="b")*(E5:E14))
If you wanted to count the number of rows where the sales goals were met, you could easily do that with SUMPRODUCT. All you need to do is put one of the arrays are two ranges with an operator in between like this: ((actual_range) > (budget_range)) * (other_criteria)
If you only wanted to compare two ranges with no other criteria you would need to use the double negative sign. The TRUE and FALSE need to be converted to 1’s and 0’s. (Having the extra criteria means the arrays will be multiplied using an arithmetic operator, converting the TRU and FALSE automatically.)
Below is an example of SUMPROEUCT to compare two columns with additional search criteria. This would be used to look at sales goals met in TX.
=SUMPRODUCT(((F5:F14)>(E5:E14))*(C5:C14="TX"))
One of the most powerful abilities of SUMPRODUCT is doing counts or sums using BOTH row and column criteria. When doing SUMPRODUCT with rows and columns, you need to make sure the range you are counting/summing has the same related column and row length.
Suppose you get a report from your IT department that lists store complaints. The rows might have the report date and manager name and columns listing the store number and location. SUMPRODUCT with rows and columns would be perfect for getting a variety of stats.
Here is the formula to sum up the number of store complaints for Allen, Store 1.
=SUMPRODUCT((C7:C16="Allen")*(D5:G5="Store1")*(D7:G16))
Here is the formula to count the number of TX stores managed by Allen with more than 3 complaints. This would be the same formula as the sum version, except we add criteria to the range of values at the end.
=SUMPRODUCT((C7:C16="Allen")*(D6:G6="TX")*(D7:G16>3))
When creating your SUMPRODUCT formulas, you might run into various errors. Here are some of the most common reasons for errors: