SUMPRODUCT Function

Multiplies matching items in ranges and adds up the results.

Category

Math & Trig

Available in

All versions, Microsoft 365

Returns

A number

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Advanced Math & Text module

Overview

SUMPRODUCT multiplies row by row and then adds the answers — quantity × price for every line, then the grand total, in one formula.

Because it works on whole arrays, it's also a flexible conditional sum: conditions become 1s and 0s that switch rows on or off.

Syntax

=SUMPRODUCT(array1, [array2], …)

Arguments

ArgumentRequired?What it does
array1YesThe first range or array.
array2, …OptionalMore ranges the same size, multiplied item by item.

Examples

Example 1 — Order total without a helper column

Quantity times price for every line, added up.

D2fx=SUMPRODUCT(B2:B4, C2:C4)
ABCD
1ItemQtyPriceOrder total
2Widget104.5153
3Gadget412
4Cable154
=SUMPRODUCT(B2:B4, C2:C4) Explain

10×4.5 + 4×12 + 15×4 = 45 + 48 + 60 = 153.

Example 2 — Conditional total

Revenue (qty × price) for the West only.

E2fx=SUMPRODUCT((A2:A4="West")*B2:B4*C2:C4)
ABCDE
1RegionQtyPriceWest revenue
2West104.5105
3East412
4West154
=SUMPRODUCT((A2:A4="West")*B2:B4*C2:C4) Explain
  1. A2:A4="West" gives TRUE, FALSE, TRUE.
  2. Multiplying turns those into 1, 0, 1 — East is switched off.
  3. 45 + 0 + 60 = 105.

Example 3 — Weighted average

Average price weighted by quantity.

=SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4) Explain

Common mistakes

  • Ranges must be the same size. Different-sized arrays return #VALUE!.

Notes

  • Text in the arrays is treated as 0.
  • Related functions: SUMIFS, MMULT, SUM.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.