Excel SUMPRODUCT Function — Multiply-Then-Add, and the Conditions SUMIFS Can't Do

TL;DR — SUMPRODUCT lines up two or more equal-sized ranges, multiplies them cell by cell, and adds up the products. Syntax: =SUMPRODUCT(array1, [array2], …). In one cell it does what a helper column plus a SUM used to do. Its two superpowers over SUMIFS: weighted totals (=SUMPRODUCT(Qty, Price)) and multi-condition logic with boolean arrays — multiply arrays for AND, add them for OR, and never put AND()/OR() inside (they collapse the array to a single value). Every array must be the same size or you get #VALUE!. Works in every version back to Excel 2003.

=SUMPRODUCT(Qty, Price)                          ' total revenue: Σ qty×price
=SUMPRODUCT((Region="West")*(Month=1)*Sales)     ' conditional sum: West AND January

Most people meet SUMPRODUCT as a mysterious formula they copied off a forum to make a "sum if two things are true" work. It deserves better. It is one of the most quietly powerful functions in Excel — a tiny array engine that lived in spreadsheets for a decade before SUMIFS existed, and still solves problems SUMIFS can't. This guide is built around the one mental model that makes it click, then the jobs it's still the right tool for.

What you'll learn

The mental model: SUMPRODUCT is a one-cell dot product
The #VALUE! trap — every array must be exactly the same size
Multi-condition logic: multiply for AND, add for OR
When you actually need the double-unary -- (and when you don't)
The two jobs SUMIFS can't do: weighted totals and OR across columns
The judgment call: SUMPRODUCT vs SUMIFS vs old CSE array formulas

The mental model: a one-cell dot product

Forget conditions for a moment. At its core SUMPRODUCT does exactly what its name says — it takes the products and sums them. Give it two columns and it multiplies row 1 by row 1, row 2 by row 2, all the way down, then adds every product into a single number:

' Qty = {2; 5; 3}, Price = {10; 4; 20}
=SUMPRODUCT(Qty, Price)     ' = 2*10 + 5*4 + 3*20 = 100

That's the whole idea. If you've ever built a helper column of =A2*B2, dragged it down, and summed it — SUMPRODUCT is that, collapsed into one cell with no helper column. Hold that picture and everything else is a variation on it: the "conditions" you'll see next are just extra arrays of 1s and 0s mixed into the same multiply-then-add.

The rule that bites first: arrays must be the same size

Here is the error you'll hit before any other. Every array you hand SUMPRODUCT has to be the same shape — same number of rows, same number of columns — because it pairs them up cell by cell. Mismatch them and you get #VALUE!:

=SUMPRODUCT(A2:A10, B2:B11)     ' #VALUE!  — 9 rows vs 10 rows
=SUMPRODUCT(A2:A10, B2:B10)     ' OK       — both 9 rows

The classic version of this bug is subtle: you build the formula, it works, then someone inserts a row inside one range but not the other, and suddenly it's #VALUE! — or worse, you anchored one range a row short and it silently pairs the wrong rows together. Make every range identical, ideally by pointing them at the same table columns (Table[Qty], Table[Price]) so they grow and shrink together. That one habit removes the entire class of mismatch errors.

Conditions: multiply for AND, add for OR

This is the part worth internalising, because it's the same logic that powers FILTER and array formulas across modern Excel. A comparison like (Region="West") doesn't return one answer — it returns a whole array of TRUE/FALSE, one per row. Excel treats TRUE as 1 and FALSE as 0 the moment you do arithmetic on it. So:

' AND — multiply the conditions: a row counts only if BOTH are 1
=SUMPRODUCT((Region="West")*(Product="Widget")*Sales)

' OR — add the conditions: a row counts if EITHER is 1
=SUMPRODUCT(((Region="West")+(Region="East"))*Sales)

Multiplying two 1/0 arrays gives 1 only where both are 1 — that's AND. Adding them gives 1 (or more) wherever either is 1 — that's OR. The killer mistake is reaching for the worksheet functions AND() and OR(): those collapse the whole array into a single TRUE/FALSE, so your formula quietly evaluates one condition for the entire column instead of row by row. The rule is blunt — inside SUMPRODUCT, use * and +, never AND()/OR().

For OR, wrap the added conditions so a row matched twice still counts once if you need a count; for a sum like the example above, double-counting can't happen because each row's Sales is added at most once per the bracket.

When you need the double-unary `--`

You'll see -- scattered through SUMPRODUCT formulas online and wonder if it's magic. It isn't. It's a double negative that forces TRUE/FALSE into 1/0. You only need it when an array of conditions has nothing to multiply against:

=SUMPRODUCT(--(Region="West"))          ' COUNT of West rows — need -- to coerce
=SUMPRODUCT((Region="West")*Sales)      ' SUM of West sales — the *Sales already coerces

In the second formula, multiplying by Sales already turns the booleans into numbers, so -- would be redundant. In the first, there's only a single boolean array and no arithmetic to trigger coercion — without -- you'd be summing TRUE/FALSE, which adds to 0. Rule of thumb: one lone condition array → add --; any condition that's multiplied by something else → it's already coerced.

The two jobs SUMIFS genuinely can't do

If all you need is a plain multi-condition AND sum, use SUMIFS — it's faster on big sheets and far easier to read. SUMPRODUCT earns its place in the two cases SUMIFS simply can't express:

' 1) WEIGHTED total / average — multiply two columns THEN sum
=SUMPRODUCT(Qty, Price) / SUM(Qty)       ' weighted average price

' 2) OR across DIFFERENT columns
=SUMPRODUCT(((Region="West")+(Channel="Online")>0)*Sales)

SUMIFS can filter, but it can't multiply two columns together before summing — so a weighted average (units × price, summed, divided by units) is out of its reach, and AVERAGEIFS only does a plain average. And SUMIFS joins its conditions with AND only; an OR that spans two different columns has no clean SUMIFS form. Those two — weighting and cross-column OR — are the reason SUMPRODUCT is still in every analyst's kit.

The judgment call: SUMPRODUCT vs SUMIFS vs CSE arrays

Three eras of the same idea, and the right choice has shifted over time:

Plain conditional sums/counts → use SUMIFS/COUNTIFS. They're optimised, readable, and the argument order never changes. Don't write SUMPRODUCT((A="x")*(B="y")*C) when SUMIFS(C,A,"x",B,"y") says the same thing.
Weighted totals, cross-column OR, multiply-then-filter → SUMPRODUCT. This is its real home and nothing newer fully replaces it in a single cell.
Legacy {=SUM(IF(…))} entered with Ctrl+Shift+Enter → retire them. SUMPRODUCT handles arrays natively with no CSE ritual, and on modern Excel FILTER covers the extract-then-sum cases more legibly. If you still see curly braces around a SUM-of-IF, it's almost always a SUMPRODUCT (or SUMIFS) waiting to happen.

The strong opinion: SUMPRODUCT is not your everyday conditional-sum tool any more — but it's the one you keep for the jobs the -IFS family can't reach, and it works identically in every version of Excel anyone still runs.

How ExcelMaster helps

The hard part of SUMPRODUCT is never the typing — it's deciding whether you need it, and getting the AND/OR and -- right when you do. ExcelMaster reads the question in business terms — weighted average cost across these orders, or total sales that are either West or sold online — and writes the formula with arrays the same size, the booleans multiplied or added correctly, and -- only where it belongs. You describe the number; it picks between SUMPRODUCT, SUMIFS, and FILTER for you.

Frequently asked questions

Why does my SUMPRODUCT return #VALUE!?

Almost always a size mismatch: two of your arrays have a different number of rows (or columns). SUMPRODUCT pairs cells one to one, so A2:A10 (9 rows) and B2:B11 (10 rows) can't line up. Make every range the exact same shape — using table columns keeps them in sync automatically. A stray text value in a numeric range can also trigger it.

How do I use SUMPRODUCT with multiple criteria?

Multiply the conditions for AND, add them for OR: =SUMPRODUCT((Region="West")*(Product="Widget")*Sales). Each condition like (Region="West") is an array of TRUE/FALSE that becomes 1/0 when multiplied. Don't use AND()/OR() inside — they collapse the array to one value.

What does the double minus (--) do in SUMPRODUCT?

It forces TRUE/FALSE into 1/0. You need it when a condition array isn't being multiplied by anything else — e.g. counting rows: =SUMPRODUCT(--(Region="West")). If you're already multiplying by a number column, the coercion happens for free and -- is redundant.

SUMPRODUCT vs SUMIFS — which should I use?

For a straight conditional sum, use SUMIFS — it's faster and clearer. Use SUMPRODUCT when you need to multiply two columns before summing (weighted totals), or to express an OR across two different columns, which SUMIFS can't do.

Does SUMPRODUCT work in older Excel?

Yes — it's one of the oldest array functions, working in every version back to Excel 2003 (and earlier), with no Ctrl+Shift+Enter required. That cross-version reliability is part of why it's stayed popular.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-30.

Related guides: Excel SUMIFS · Excel COUNTIFS · Excel SUBTOTAL · Excel AGGREGATE · Excel FILTER