SUMPRODUCT in Excel: what it does and how to use it

SUMPRODUCT multiplies two ranges together row by row, then adds up all the results, in one step.

Syntax

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

When to use it

Weighted averages, order totals from quantity and price, any sum where each row has to be multiplied before it is added.

What trips people up

Both ranges must be the same size. If they are not, the answer comes back as #VALUE!.

Watch it built, step by step

Weighted average — When some rows count for more

Questions

Do I need Ctrl+Shift+Enter?

No. SUMPRODUCT works through ranges by itself in every version of Excel, which is why it was the standard way to do conditional sums before SUMIFS.

How do I add a condition?

Multiply by the test: =SUMPRODUCT((A2:A100="North")*B2:B100*C2:C100) only counts the North rows.

How do I get a weighted average?

=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10), where B holds the values and C the weights.

Related functions

SUM — SUM adds up everything in the range you give it, ignoring any text and empty cells along the way.
SUMIFS — SUMIFS adds up the numbers in one column, but only on the rows that satisfy every condition you give it.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.