Excel SUMPRODUCT Function

SUMPRODUCT multiplies the corresponding elements in two or more arrays, then adds up all the products into one number. Feed it a single array and it just sums that array — no multiplication needed. The behavior that trips up even experienced users: every array you pass in must have identical dimensions, or SUMPRODUCT throws #VALUE! with no indication of which array is the problem.

Available in: Excel 2003 and every version since, including Excel 365, 2021, and 2019. The core multiply-and-sum behavior hasn't changed in over two decades. Nesting a dynamic array function like FILTER directly inside SUMPRODUCT — shown later in this article — requires Excel 365 or Excel 2021.

Syntax

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

Parameter	Required	Description
`array1`	Yes	The first array or range. Can be a plain range, a Boolean comparison (`B2:B50="East"`), or a calculated array (`C2:C50*1.08`). Pass only `array1` and SUMPRODUCT skips multiplication entirely — it behaves like `SUM`, adding up whatever values or 1s/0s that single array contains.
`array2`, `array3`, ...	No	Additional arrays multiplied element-by-element against `array1` and each other. You add a second or third array when you need more than one condition to be true at once — each extra Boolean array (`region="East"`, `reps="Diaz"`) narrows the rows that survive to the final sum. Every array must share the exact same number of rows and columns.

⚠

SUMPRODUCT does not validate that arrays match in size before calculating — if array1 is 50 rows and array2 is 51, the formula fails with #VALUE!. This is the single most common cause of broken SUMPRODUCT formulas. Always select ranges with the same start and end row.

Basic Example

You're totaling an invoice. Column C holds units sold per line item, column D holds price per unit, and you want the invoice total without adding a helper column that multiplies the two.

=SUMPRODUCT(quantities, unit_prices)

// quantities  — C2:C24, units sold per line item
// unit_prices — D2:D24, price per unit for the same rows

SUMPRODUCT multiplies row 2's quantity by row 2's price, row 3's quantity by row 3's price, and so on down to row 24. Then it adds all 23 products together and returns one total. Compare that to =SUMPRODUCT(quantities*unit_prices) — mathematically identical, since a single array argument is just shorthand for "multiply these first."

How SUMPRODUCT Works

Array dimensions must match exactly

SUMPRODUCT pairs up array elements by position: item 1 in array1 with item 1 in array2, item 2 with item 2, and so on. If the arrays don't line up — different row counts, or a range that includes a header row while another doesn't — the formula returns #VALUE! instantly. There's no partial match, no approximate alignment. Rows count, always.

Boolean comparisons become 1s and 0s

A condition like B2:B50="East" doesn't return text. It returns an array of TRUE/FALSE values, one per row. When you multiply that array against another number, Excel coerces TRUE to 1 and FALSE to 0. That's the entire mechanism behind every conditional SUMPRODUCT formula you'll ever write.

=SUMPRODUCT((region="East")*commissions)

// region      — B2:B200, converted to an array of 1s and 0s
// commissions — D2:D200, multiplied by that array
// Rows where region ≠ "East" get multiplied by 0 and drop out of the total

Multiplication behaves like AND. Addition behaves like OR

Multiplying two condition arrays together requires both conditions to be true for a row to count — 1 × 1 = 1, but 1 × 0 = 0. Addition works differently: (region="East")+(region="West") gives you 1 if either condition is true, and 0 if neither is. Because a single cell in the region column can only equal "East" or "West" — never both — the two conditions are mutually exclusive, so the sum for any row is always 0 or 1. It never reaches 2. That's what makes this safe as OR logic: you're not double-counting rows, because no row can satisfy both conditions at once.

// AND: rep is "Diaz" AND region is "East"
=SUMPRODUCT((reps="Diaz")*(region="East")*commissions)

// OR: region is "East" OR region is "West"
=SUMPRODUCT(((region="East")+(region="West"))*commissions)

Note the parentheses around the addition in the OR example. Without them, (region="East")+(region="West")*commissions evaluates in the wrong order entirely.

⚠

If your OR conditions aren't mutually exclusive — for example, checking two criteria that could both be true on the same row — addition can produce a 2, which overcounts that row. Use ((condition1)+(condition2)>0) instead, wrapping the sum in a comparison that forces it back to 1 or 0 before multiplying.

It never needs Ctrl+Shift+Enter

SUMPRODUCT is one of the few functions built to handle array math natively in a plain formula bar entry. Older array formulas required Ctrl+Shift+Enter to force array evaluation; SUMPRODUCT skips that requirement completely, in Excel 2003 and every version since. This is a big part of why it survived the shift to dynamic arrays without needing a rewrite.

It works on closed, external workbooks

SUMIFS, COUNTIFS, and most conditional functions require the source workbook to be open to calculate a result if you're referencing an external file. SUMPRODUCT doesn't have that restriction — it can pull from a closed workbook and still return a live value. That makes it a practical option for reporting models that link to a source file that isn't always open, though it's worth weighing against the tradeoffs: external links break if the source file moves or is renamed, and formulas referencing closed workbooks can be slower to audit than a live SUMIFS pointed at an open sheet.

Common Use Cases

Multi-criteria conditional sum (AND logic)

You need total commission paid to a specific rep, but only for deals in a specific region.

=SUMPRODUCT((reps="Patel")*(region="Midwest")*commissions)

// reps       — must equal "Patel"
// region     — must equal "Midwest"
// commissions — the values summed when both conditions are true

Weighted average without a helper column

You're calculating a student's final grade from four assignments with different weights, and you don't want a separate multiplication column cluttering the sheet.

=SUMPRODUCT(scores, weights) / SUM(weights)

// scores  — C2:C5, the four assignment grades
// weights — D2:D5, each assignment's weight (e.g., 0.1, 0.2, 0.3, 0.4)

Dividing by SUM(weights) normalizes the result even if the weights don't add up to exactly 1.

Counting matches across multiple criteria

You want a count, not a sum — how many orders shipped late from a specific warehouse.

=SUMPRODUCT((warehouse="W3")*(ship_status="Late"))

// No third array needed — SUMPRODUCT sums the 1s and 0s directly,
// which gives you a count of rows meeting both conditions

Case-sensitive matching

Standard Excel comparisons ignore case, but payroll or ID audits sometimes need exact-case matching. Nest EXACT inside the array argument.

=SUMPRODUCT(EXACT(employee_ids, "EMP-045")*hours_worked)

// EXACT returns TRUE only for a case-sensitive match on "EMP-045"
// hours_worked sums only for that exact-case ID

Handling Errors

SUMPRODUCT throws two errors in practice: #VALUE! and #REF!. They come from different causes, so wrapping the whole formula in a single generic IFERROR message hides which one you're actually dealing with — and that matters, because the fix is different for each.

Common causes of #VALUE!:

Arrays passed to the same SUMPRODUCT formula don't have matching row or column counts
One array contains text where a number is expected, and that text can't be coerced (a genuine text string, not TRUE/FALSE)
A comparison array (like region="East") is combined with a differently sized range

Common causes of #REF!:

A row or column referenced in one of the arrays was deleted after the formula was written

Before wrapping the formula, check ERROR.TYPE to find out which one you're dealing with — it returns 3 for #VALUE! and 4 for #REF!:

=ERROR.TYPE(SUMPRODUCT((region="East")*(reps="Diaz")*commissions))

Once you know the cause, a targeted fallback is more useful than a generic one:

=IFERROR(SUMPRODUCT((region="East")*(reps="Diaz")*commissions), "Check array sizes")

✓

When SUMPRODUCT returns #VALUE! and you can't spot the mismatch by eye, select each array argument individually in the formula bar and check the row count in the Name Box. A one-row difference between two ranges is the usual culprit. Diagnose with ERROR.TYPE first — don't rely on a single catch-all message to tell you what went wrong.

Notes & Gotchas

Why does SUMPRODUCT return #VALUE!?

The most common cause is mismatched array dimensions — one range covers 48 rows, another covers 50. SUMPRODUCT requires every array argument to have identical row and column counts, and it won't tell you which array is wrong. Fix it by re-selecting all ranges to start and end on the same rows.

Does SUMPRODUCT support wildcards?

No, not within a standard equality comparison. When you write region="Eas*", SUMPRODUCT treats the asterisk as a literal character, not a wildcard — so it looks for an exact string match on "Eas*" and finds nothing. This is specific to array comparisons using =; SUMIF and SUMIFS interpret wildcards correctly because they use a different matching engine internally. To get partial-match behavior inside SUMPRODUCT, combine it with ISNUMBER(SEARCH(...)) instead of a direct equality comparison.

=SUMPRODUCT(ISNUMBER(SEARCH("east", region))*commissions)

Can SUMPRODUCT reference closed workbooks?

Yes. SUMPRODUCT can pull values from an external workbook that isn't open, which SUMIFS and COUNTIFS cannot do reliably. This makes it a workaround when a reporting model links to a source file that isn't always open, such as a monthly extract stored on a shared drive — just be aware that the link breaks if that file gets moved or renamed.

Why is SUMPRODUCT slow on whole-column references?

Whole-column references like A:A force SUMPRODUCT to evaluate over a million rows even if only 200 contain data. Because SUMPRODUCT processes every row as an array operation rather than short-circuiting on blanks, this slows down recalculation of that worksheet — every additional SUMPRODUCT formula using a whole-column reference adds to the same delay. Always bound ranges to the actual data — A2:A5000 instead of A:A — especially in models with several SUMPRODUCT formulas.

Does SUMPRODUCT need Ctrl+Shift+Enter?

No. SUMPRODUCT was built to process array math with a plain Enter key, unlike older array formulas that required Ctrl+Shift+Enter to force array evaluation. This has been true since Excel 2003, and it still holds in Excel 365's dynamic array engine.

✓

In Excel 365, SUMPRODUCT plays well with dynamic array functions like FILTER. You can nest FILTER inside SUMPRODUCT to sum a subset of a table without writing out the Boolean logic by hand: =SUMPRODUCT(FILTER(commissions, region="East")).

Related Functions

Function	Use this when...
`SUMIFS`	Your criteria are simple equality or range matches across separate columns, all in the same open workbook, and you don't need OR logic.
`SUMIF`	You only have one condition to check — SUMPRODUCT's array math is overkill for a single criterion.
`COUNTIFS`	You're counting rows on multiple AND conditions and don't need the flexibility of custom array math or OR logic.