The Excel SUMPRODUCT Function
Sample Functions
Summary
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.
Common Uses
While SUMPRODUCT was intended to multiply then sum ranges, it’s commonly used to do the following:
- Count or sum cells that contains criteria in both the rows and columns
- Sum or count that contain an OR criteria (as opposed to using multiple SUMIFS or COUNTIFS)
- Sum or count a range of data by year only
- Sum or count a range of data by a short month name
- Count the comparison between two ranges (for example, how many salespeople met their sales goal – Column A vs B)
- Sum or count when you are using a source file that will usually be closed (SUMPRODUCT will work when the source file is closed. SUMIFS and COUNTIFS will not work with closed workbooks)
Syntax
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. |
Double Negative Sign (–)
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.
Asterisk (*) vs Comma (,) Syntax
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.
Syntax Best Practices
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.
Standard SUMPRODUCT Example
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 With Criteria for Counts and Sums
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.
Count With One Criteria
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 +
Count With Multiple Criteria
You could count the number of sales reps in TX that handle Auto using the formula below.
=SUMPRODUCT((C5:C14="tx")*(D5:D14="auto"))
Sum With One Criteria
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 +
Sum With Multiple Criteria
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))
Using OR Criteria (same column)
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))
Using OR Criteria (different columns)
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 +
Advanced Count and Sums
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.
Sum a Range by Year
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))
Sum a Range by Short Month Name
SUMPRODCUT makes it simple to even summarize by the short month name, for example, “Jan”.
=SUMPRODUCT((TEXT(D5:D14,"MMM")="Jan")*(E5:E14))
Case Sensitive Sum
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))
Sum and Ignore Text Values
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)
Partial Match – String Contains
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))
Partial Match – String Begins or Ends With
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))
Counts Comparing Two Columns
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"))
SUMPRODUCT Row and Column Criteria
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.
SUMPRODUCT Row and Column – Sum
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))
SUMPRODUCT Row and Column – Count
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))
Common SUMPRODUCT Erorrs
When creating your SUMPRODUCT formulas, you might run into various errors. Here are some of the most common reasons for errors:
- There is text inside of the values you are summing, resulting in a #VALUE error. You would want to use the IFERROR trick above to ignore the text.
- You have a search range that includes multiple columns or a single column with invalid data. For example, looking at multiple columns for a YEAR, but one or both columns contain text and not dates. The YEAR function would then return a #VALUE error.
- Your formula returns blank or 0 when things look to be correct. If you are searching for a number or year, make sure your search criteria is written as the number and not text (eg, 2022 as opposed to “2022”)
- An #NA error when doing a column sum or count. This would be because your column ranges are not equal. Make sure all column ranges have the same starting and ending row numbers.
- An #NA error when doing a row and columns sum or count. This would also be because the row, column, and value criteria ranges are not equal.