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.
Multiplies matching cells then sums the products. One array sums; a condition must be coerced (-- or ×value) to count or sum.
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.
| Expression | Result | Why |
|---|---|---|
| =SUMPRODUCT(B2:B6,C2:C6) | 445 | Weighted total: qty × price |
| =SUMPRODUCT((D2:D6>80)*C2:C6) | 85 | Conditional sum: price where score>80 |
| =SUMPRODUCT(--(D2:D6>80)) | 4 | Count rows with score>80 |
| =SUMPRODUCT(B2:B6) | 20 | A single array just sums |
| =SUMPRODUCT(B2:B6,B2:B6) | 90 | Sum of squares of qty |
| =SUMPRODUCT((D2:D6>80)*B2:B6) | 16 | Sum qty where score>80 |
| =SUMPRODUCT({1,2,3},{4,5,6}) | 32 | Array constants: 1*4+2*5+3*6 |
| =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6) | 22.25 | Weighted average price |
| Expression | Result | Why |
|---|---|---|
| =SUMPRODUCT(--(D2:D6>80)) | 4 | -- turns TRUE/FALSE into 1/0 so they sum |
| =SUMPRODUCT((D2:D6>80)*C2:C6) | 85 | condition × value sums only matching rows |
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.
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.
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.
| Platform | Support | Notes | Gotcha |
|---|
SUMPRODUCT is one piece. The full course walks the toolkit end to end with live, measured examples.