If you spend much time working with Excel formulas, you'll start to run into the SUMPRODUCT function a lot. SUMPRODUCT seems to be the catch-all, do-all, go-to solution for many seemingly unrelated Excel problems. Why is SUMPRODUCT in so many Excel formulas?
The main reason SUMPRODUCT appears so often in Excel formulas is that it supports array operations natively, and array operations combined with Boolean logic are a very good way to solve many problems in Excel. In the past (Excel 2019 and older) Excel's formula engine did not handle most array operations without special handling. As a result, SUMPRODUCT has always been a simple way to create an array formula that "just works". In the current version of Excel, these limitations are gone, so it is possible to use the SUM function instead.
Note: as mentioned above, the technique of using SUMPRODUCT to solve general problems in Excel often involves some kind of Boolean logic. If this concept is new to you, this video provides a basic overview. The video was created in Excel 365, so I am using the SUM function, but SUMPRODUCT would work just as well.
The SUMPRODUCT function
The purpose of SUMPRODUCT is to calculate the sum of products. The worksheet below shows a classic example: SUMPRODUCT is used to calculate the sum of Price * Qty:
In this worksheet, there is no helper column that calculates the "Extended price" for each item. Instead, SUMPRODUCT calculates the intermediate values by multiplying the two ranges together and returns a sum in one step. Notice we are providing C5:C9 as array1 and D5:D9 as array2. So far, so good. SUMPRODUCT performs a useful calculation, but there seems to be nothing special about it.
SUMPRODUCT and array operations
In the formula above, we are using two separate arguments, array1 and array2:
=SUMPRODUCT(C5:C9,D5:D9)
Things get more interesting if we alter the structure of this formula and combine the two arguments into one argument like this:
=SUMPRODUCT(C5:C9*D5:D9)
In this formula, we multiply the two ranges together inside array1, using what is called an "array operation". The formula evaluates like this:
=SUMPRODUCT(C5:C9*D5:D9)
=SUMPRODUCT({10.5;8;11.75;7.74;9}*{5;6;10;4;8})
=SUMPRODUCT({52.5;48;117.5;30.96;72})
After multiplication, there is just one array given to SUMPRODUCT as array1. The final result is exactly the same as the original formula.
You will see this pattern frequently in SUMPRODUCT — various math operations combined in array1 — because it provides more control over the logic used to manage data. When separate arguments are used, SUMPRODUCT multiplies arguments, which works like AND logic in Boolean algebra. Using one argument means you can use addition (+) for OR logic, or other math operations as needed. As a bonus, any math operation will automatically convert TRUE and FALSE values to 1s and 0s, which are frequently needed to tally up results. Finally, this flexibility means SUMPRODUCT can solve all kinds of tricky problems without Control + Shift + Enter, which is why it's the go-to function in so many formulas.
A further reason SUMPRODUCT is used so often is that it can handle conditional counts and sums in ways that COUNTIFS and SUMIFS simply can't. This is because these functions require ranges and can't use arrays directly. Examples: Count birthdays by year, Sum by year.
The SUM function
In the formula above, notice that we have just a single array after multiplication. When SUMPRODUCT is given one array, it simply returns a sum. In that case, you might wonder if we can replace the SUMPRODUCT function with the SUM function like this:
=SUM(C5:C9*D5:D9)
The answer is: it depends. In modern versions of Excel that include the new dynamic array engine, you can indeed use SUM instead of SUMPRODUCT. However, in older versions of Excel, the SUM version of the formula must be entered as an array formula with Control + Shift + Enter. If not, the formula returns an incorrect result:
In older versions of Excel (2010, 2016, 2019), the SUM version of the formula returns an incorrect result when the formula is entered normally. In Excel 365, the formula works just fine:
Why does the formula work in SUMPRODUCT but not SUM? Recall that multiplying the two ranges together is an "array operation". It turns out that SUMPRODUCT is in a small group of functions that can handle most array operations natively. The SUM function is not in this group and must be entered with Control + Shift + Enter when arguments include array operations.
Note: in the example above, we are using just one argument. When only one argument is provided, both SUM and SUMPRODUCT return a sum. With more than one argument, SUM and SUMPRODUCT have different behaviors. SUM returns a sum, while SUMPRODUCT returns the sum of products.
The past
The main reason SUMPRODUCT appears so often in Excel formulas is that it supports array operations natively, and array operations combined with Boolean logic turn out to be a very good way to solve many problems in Excel. In the past (Excel 2019 and older) Excel's formula engine did not handle most array operations without special handling. As a result, SUMPRODUCT has always been a simple way to create an array formula that "just works".
In the past, you could use the SUM function instead of SUMPRODUCT in formulas that use array operations, but SUM required Control + Shift + Enter. This means if someone forgets to use CSE when checking or adjusting a formula, the result might change, even if the formula did not change. SUMPRODUCT avoids this problem. It also avoids the need to explain Control + Shift + Enter, which is a complicated topic.
The present
Since dynamic array formulas were introduced in Excel 365, the need for SUMPRODUCT has started to diminish, because array formulas are natively supported. This means you can replace the SUMPRODUCT function with the SUM function in formulas that use an array operation and get the same behavior. To illustrate with another example, the worksheet below uses the LEN function to count the total number of characters in the range B5:B9.
=SUMPRODUCT(LEN(B5:B9))
Because the range contains five cells, the LEN function returns an array with five counts:
LEN(B5:B9) // returns {5;5;4;6;4}
This is another kind of array operation called lifting. The array from LEN is returned to SUMPRODUCT as array1:
=SUMPRODUCT({5;5;4;6;4}) // returns 24
And SUMPRODUCT returns 24 as a final result. This formula needs no special handling; it will work in any version of Excel.
In "modern" versions of Excel, the SUM version of the formula works exactly the same way:
=SUM(LEN(B5:B9))
However, in Legacy Excel the SUM version fails. The screen below shows the same formula in Excel 2010:
=SUM(LEN(B5:B9))
Note that curly braces are not visible in the formula bar. This confirms the formula was not entered with CSE. Below is the same formula, this time entered with Control + Shift + Enter:
{=SUM(LEN(B5:B9))}
Now the formula returns a correct result. The curly braces in the formula bar confirm the formula was entered with Control + Shift + Enter.
Note: the curly braces are added by Excel automatically when a formula is entered as an array formula with Control + Shift + Enter. Do not add curly braces manually or the formula will not work.
Automatic array formula conversion
To prevent formulas from breaking in older versions of Excel, Excel will automatically convert array formulas to use the array syntax*. This means you will see curly braces in the formula bar even when a formula was never entered with Control + Shift + Enter. For example, the SUM formula above will appear like this if opened in Excel 2016:
{=SUM(LEN(B5:B9))}
Note this is automatic behavior to prevent the formula from returning a different result in older versions of Excel. If the formula is re-entered without Control + Shift + Enter in an older version of Excel, the formula will return an incorrect result.
* Excel is quite conservative in how it evaluates array formulas and you will sometimes see curly braces added to formulas that work just fine without them. For example, you will see the curly braces added to a SUMPRODUCT formula that uses array operations, even when they are not needed. The only way to be sure if the array syntax is needed is to re-enter the formula normally and check the result.
Summary
SUMPRODUCT is in a small group of functions that can handle array operations natively, without Control + Shift + Enter. By placing various operations into a single argument, you can extract data with other functions, and use Boolean algebra to create AND and OR logic in many different ways. This has made SUMPRODUCT the go-to solution for tricky problems over the years.
In Excel 365 and Excel 2021 the formula engine handles arrays natively. This means you can often use the SUM function in place of SUMPRODUCT with the same result and no need to enter the formula in a special way. However, if the same formula is opened in an earlier version of Excel, it will require Control + Shift + Enter.
If you need compatibility with older versions of Excel, SUMPRODUCT is a safer and more robust option, since it "just works" in almost all cases. If you will only be using a worksheet in modern versions of Excel (with the new dynamic array engine), the SUM function can be used instead of SUMPRODUCT and will work just fine. If you are not sure what version of Excel a worksheet will be used with, SUMPRODUCT is probably the better option, since it avoids complexity.
More Examples
The formulas below use SUMPRODUCT for compatibility with older versions of Excel, but you can use the SUM function instead in modern Excel.
- Count if row meets internal criteria
- Count birthdays by year
- Count cells equal to case sensitive
- Count cells that begin with
Workbook note
The attached workbook below contains the examples used in the article above. Keep in mind that if you open this workbook in older versions of Excel, you will see that the formulas with array operations have already been converted to array formulas. Look for the curly braces in the formula bar, and notice they disappear if you edit the formula. To see the formula fail without this special handling, re-enter the formula normally (i.e. don't use Control + Shift + Enter).
