If you spend much time working with Excel formulas, you’ll start to run into theSUMPRODUCT functiona 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?
If this concept is new to you,this video provides a basic overview.
The SUMPRODUCT function
The purpose of SUMPRODUCT is to calculate the sum of products.
Notice we are providing C5:C9 asarray1and D5:D9 asarray2.
So far, so good.
SUMPRODUCT performs a useful calculation, but there seems to be nothing special about it.
The formula evaluates like this:
After multiplication, there is just one array given to SUMPRODUCT asarray1.
The final result is exactly the same as the original formula.
When separate arguments are used, SUMPRODUCTmultipliesarguments, whichworks like AND logic in Boolean algebra.
This is becausethese functions require rangesand can’t use arrays directly.
Examples:Count birthdays by year,Sum by year.
When SUMPRODUCT is given one array, it simply returns a sum.
Recall that multiplying the two ranges together is an “array operation”.
It turns out that SUMPRODUCT is in asmall group of functionsthat can handle most array operations natively.
Note: in the example above, we are using just oneargument.
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.
SUMPRODUCT avoids this problem.
It also avoids the need to explain Control + Shift + Enter, which is a complicated topic.
The array from LEN is returned to SUMPRODUCT asarray1:
And SUMPRODUCT returns 24 as a final result.
This formula needs no special handling; it will work in any version of Excel.
This confirms the formula wasnotentered withCSE.
The curly braces in the formula bar confirm the formula was entered with Control + Shift + Enter.
Do not add curly braces manually or the formula will not work.
This has made SUMPRODUCT the go-to solution for tricky problems over the years.
InExcel 365and Excel 2021 the formula enginehandles arrays natively.
Workbook note
The attached workbook below contains the examples used in the article above.
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).
