Math & trig · arrays · Nov 16, 2026

The SUMPRODUCT function,
measured & explained.

SUMPRODUCT multiplies matching cells across arrays, then adds the products — no helper column and no Ctrl+Shift+Enter. With a condition it becomes a conditional sum or count. This page measures weighted totals, the -- coerce, and weighted averages on a live table.

D1
fx
=SUMPRODUCT(B2:B6, C2:C6)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How SUMPRODUCT works

Multiplies matching cells then sums the products. One array sums; a condition must be coerced (-- or ×value) to count or sum.

=SUMPRODUCT(array1, [array2], …)
array1The first range or array.
array2, …Optional further arrays; all must be the same size.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Multiply, then sum

What SUMPRODUCT actually returns

SUMPRODUCT multiplies matching cells across arrays, then adds the products — no helper column, no Ctrl+Shift+Enter. With a condition it becomes a conditional sum or count. Every row measured live on the sample table. Click any row to load it.

Weighted & conditional gallery

ExpressionResultWhy
=SUMPRODUCT(B2:B6,C2:C6)445Weighted total: qty × price
=SUMPRODUCT((D2:D6>80)*C2:C6)85Conditional sum: price where score>80
=SUMPRODUCT(--(D2:D6>80))4Count rows with score>80
=SUMPRODUCT(B2:B6)20A single array just sums
=SUMPRODUCT(B2:B6,B2:B6)90Sum of squares of qty
=SUMPRODUCT((D2:D6>80)*B2:B6)16Sum qty where score>80
=SUMPRODUCT({1,2,3},{4,5,6})32Array constants: 1*4+2*5+3*6
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)22.25Weighted average price

The double-negative coerce

ExpressionResultWhy
=SUMPRODUCT(--(D2:D6>80))4-- turns TRUE/FALSE into 1/0 so they sum
=SUMPRODUCT((D2:D6>80)*C2:C6)85condition × value sums only matching rows
⚠ Arrays must be the same size — SUMPRODUCT(B2:B6,C2:C4) is #VALUE!. A bare condition (D2:D6>80) is TRUE/FALSE; multiply by a value or use -- to coerce it to 1/0, otherwise it sums to 0.
Weighted average = SUMPRODUCT(values,weights)/SUM(weights). SUMPRODUCT handles arrays natively — no CSE needed — which is why it powers conditional sums and counts before SUMIFS existed.
// 03 — Try it live

Interactive SUMPRODUCT playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see SUMPRODUCT compute a weighted total, a conditional sum, a count with --, and a weighted average.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where SUMPRODUCT works

SUMPRODUCT is available in Excel 2003+, Microsoft 365, Excel for the web and Google Sheets. It handles arrays natively (no CSE). This page avoids ROW()-array patterns, which some engines expand differently.

PlatformSupportNotesGotcha
✓ Supported full SUMPRODUCT◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master array math

SUMPRODUCT is one piece. The full course walks the toolkit end to end with live, measured examples.

  • Weighted totals and weighted averages
  • Conditional sums and counts without CSE
  • The -- double-negative coerce
  • Same-size array rule and the #VALUE! trap
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked