The Excel SUMPRODUCT function, explained interactively.
SUMPRODUCT multiplies corresponding elements of two or more arrays and returns the sum of those products — a single scalar. It’s the one-formula expression of the dot product: element-wise multiplication, then addition. The canonical use is revenue totals from units × price, but the trick of multiplying boolean arrays makes it a versatile SUMIFS substitute that survives in every version of Excel back to 2003.
How to use SUMPRODUCT
Pass two ranges of equal size — SUMPRODUCT multiplies them row-by-row and adds up the products. Open the playground to swap between datasets and watch the total recompute live.
- Type
=SUMPRODUCT(and supply the first array — a range of numbers. Usually the first factor in the multiplication: units, scores, shares, or a boolean condition array. - Add a comma, then the second array — the matching range of equal size. Row
iof array2 pairs with rowiof array1 and contributes their product to the final sum. - Close the parenthesis. SUMPRODUCT returns a single number — the sum of all element-wise products. No array formula shortcut (Ctrl+Shift+Enter) is needed; SUMPRODUCT handles arrays natively.
- For conditional sums, wrap each condition in parentheses and multiply the resulting boolean arrays together with the value range:
=SUMPRODUCT((A2:A20="East")*B2:B20).
array2 or SUMPRODUCT returns #VALUE!.i contributes Bi × Ci to the final sum. Text cells silently count as zero.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Units | Price | B × C |
| 2 | Widget A | 120 | 4.50 | 540.00 |
| 3 | Widget B | 85 | 7.20 | 612.00 |
| 4 | Widget C | 200 | 2.99 | 598.00 |
| 5 | Widget D | 60 | 12.00 | 720.00 |
| 6 | Widget E | 150 | 5.50 | 825.00 |
| 7 | ||||
| 8 | SUMPRODUCT result | 3,295.00 | ||
=SUMPRODUCT(B2:B6, C2:C6). Excel computes B×C per row (column D), then sums the five products: 3,295.00.SUMPRODUCT syntax and arguments
One required array and up to 254 additional arrays. All arrays must share the same dimensions. See Microsoft’s official SUMPRODUCT reference for the full specification.
B2:B20), a named range, a constant array ({1;2;3;4}), or any formula returning an array. Text and empty cells inside the range are silently treated as zero — they contribute nothing to the total instead of raising an error.array1. Five-row × one-column paired with four-row × one-column returns #VALUE!.- Supplying only
array1makes SUMPRODUCT equivalent toSUM— the element-wise product of a single array is just the array itself. - Supplying three arrays computes
Σ array1ᵢ × array2ᵢ × array3ᵢ. - Boolean expressions (
A2:A20="East") produce a TRUE/FALSE array that coerces to 1/0 under multiplication — the foundation of SUMPRODUCT’s conditional-sum trick.
SUMPRODUCT examples
Four recurring patterns: revenue total, weighted grade, multi-condition sum, and conditional row count.
Example 1: SUMPRODUCT for revenue totals (units × price)
The textbook use case. Column B stores units sold; column C stores price per unit. SUMPRODUCT multiplies them row-by-row and sums — one formula, no helper column.
Returns the total revenue across five widget lines. Adding a product line simply means extending both ranges to the new last row — no other formula edits required.
Example 2: SUMPRODUCT for weighted averages
Scores × weights → weighted grade. When weights sum to 1, SUMPRODUCT returns the weighted average directly. When they don’t, divide by SUM(weights): =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6).
Replaces a tedious B2*C2 + B3*C3 + B4*C4 + B5*C5 + B6*C6 chain with one compact call. Extends to any number of rows without editing the formula structure.
Example 3: SUMPRODUCT as a multi-condition SUMIFS
Each (condition) returns a TRUE/FALSE array. Multiplying them together coerces to 1/0, zeroing out any row that fails any condition. The surviving values pass through C2:C7 and SUMPRODUCT totals them.
Returns the total for East region in Q2 only. The same answer as SUMIFS but works in Excel 2003 and handles array-returning functions that SUMIFS cannot.
Example 4: SUMPRODUCT to count rows meeting a condition
Multiply a boolean array by 1 (or double-negate with --) to convert TRUE/FALSE into 1/0. SUMPRODUCT then adds the 1s — effectively a COUNTIF that handles any boolean expression.
Returns 5 — the count of students scoring 80 or higher. Swap *1 for -- for the same effect with slightly better performance: =SUMPRODUCT(--(B2:B8>=80)).
Common SUMPRODUCT errors and fixes
Two recurring failure modes — one visible error, one silent undercount that looks plausible until you check.
SUMPRODUCT returns #VALUE! on size mismatch
Cause: the arrays have different dimensions. B2:B6 (5 rows) paired with C2:C4 (3 rows) triggers #VALUE! — SUMPRODUCT cannot line up elements when the shapes disagree.
Verify every range in the formula ends on the same row (and starts on the same row). A common slip: copy-pasting an older formula where the data has since grown, leaving one range at the old shorter length.
SUMPRODUCT silently drops text-number rows
Cause: when a cell holds a number formatted as text (apostrophe prefix, or imported from a text source), SUMPRODUCT treats it as zero rather than raising an error. The whole row contributes nothing and the total is quietly understated.
To fix: click the affected cell and look for the green triangle or an apostrophe in the formula bar. Retype the number without the apostrophe, or bulk-fix with VALUE() in a helper column: =VALUE(B2). To catch the issue proactively, use =ISNUMBER(B2) audit checks on the source columns.
SUMPRODUCT vs SUMIFS, SUMIF, and array formulas
SUMPRODUCT predates most of its alternatives — it’s often the most compatible choice even when cleaner modern functions exist.
| Function | Best for | Handles array math? | Available in |
|---|---|---|---|
SUMPRODUCT | Element-wise products, weighted totals, multi-condition sums, counts with boolean expressions | Yes — natively, no CSE needed | Every version since Excel 2003 & Google Sheets |
| SUMIFS | Straight multi-condition sums on a single value column | No — conditions are compared, not multiplied | Excel 2007+ & Google Sheets |
| SUMIF | Single-condition sum with an optional sum_range | No | Excel & Google Sheets |
Legacy CSE array formula {=SUM(B2:B20*C2:C20)} | Older workbooks without SUMPRODUCT | Yes — but needs Ctrl+Shift+Enter | Excel pre-365 (avoid; use SUMPRODUCT) |
Modern dynamic-array =SUM(B2:B20*C2:C20) | Modern Excel users who don’t need backward compatibility | Yes — via dynamic array calculation | Excel 365 & Excel 2021+ |
Rule of thumb: for plain multi-condition sums, reach for SUMIFS — it’s the clearest reader experience. Switch to SUMPRODUCT when you need array-level arithmetic inside the formula (mixing comparison operators with multiplication) or when the workbook must run in older Excel versions. SUMPRODUCT remains the universal cross-tool, cross-version answer.
SUMPRODUCT frequently asked questions
6.01What does SUMPRODUCT do in Excel?▸
SUMPRODUCT multiplies corresponding elements of two or more arrays, then sums the products. With arrays of equal length, =SUMPRODUCT(A1:A5, B1:B5) computes A1×B1 + A2×B2 + … + A5×B5. It’s the fastest way to compute weighted totals (revenue = units × price) and a common SUMIFS alternative that handles array conditions directly.
6.02How do I use SUMPRODUCT with multiple conditions?▸
Wrap each condition in parentheses so it evaluates to a TRUE/FALSE array, then multiply them together along with the value range: =SUMPRODUCT((A2:A20="East")*(B2:B20="Q2")*C2:C20). The boolean arrays coerce to 1/0 under multiplication, zeroing out rows that fail any condition. This is the classic SUMIFS alternative — older versions of Excel didn’t have SUMIFS, so SUMPRODUCT filled the gap.
6.03Why does SUMPRODUCT return #VALUE!?▸
Almost always because the arrays have different sizes. SUMPRODUCT requires all arrays to have identical shape —B2:B10 (9 rows) paired with C2:C6 (5 rows) triggers #VALUE!. Double-check every range in the formula ends on the same row. A related cause: text values inside a range that’s being multiplied — text × number is #VALUE! unless you coerce with -- or +.
6.04SUMPRODUCT vs SUMIFS — which should I use?▸
For a simple conditional sum, SUMIFS is clearer: =SUMIFS(C2:C20, A2:A20, "East", B2:B20, "Q2"). Prefer SUMPRODUCT when (a) you need array-level arithmetic beyond simple filtering — e.g., weighted counts or conditions that reference cells dynamically, (b) you’re supporting Excel 2003 or earlier where SUMIFS doesn’t exist, or (c) conditions involve array operations SUMIFS can’t express, such as =SUMPRODUCT((MONTH(A2:A20)=3)*B2:B20).
6.05Can SUMPRODUCT replace array formulas (CSE formulas)?▸
Yes — this was SUMPRODUCT’s original appeal before dynamic arrays. It evaluates array arguments natively without requiring Ctrl+Shift+Enter. For example, =SUMPRODUCT(LEN(A2:A20)) returns the total character count across all cells in one formula — a task that otherwise required a legacy CSE array formula. In modern Excel 365, =SUM(LEN(A2:A20)) also works thanks to dynamic arrays, but SUMPRODUCT remains backward-compatible across every version.
6.06Does SUMPRODUCT work the same in Google Sheets?▸
Yes. SUMPRODUCT has the same signature and behavior in Google Sheets, including the multi-argument array form and the boolean-multiplication trick for conditional sums. Sheets also offers ARRAYFORMULA as an alternative, but SUMPRODUCT remains the cross-tool standard. Formulas written in either product port directly to the other without changes.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.