Excel SUMPRODUCT with multiple criteria: a working guide

The fastest way to learn whether you actually need SUMPRODUCT is to try to sum revenue for “West region, Q3, channel = Retail” and watch SUMIFS handle it in a single line — until the day you need OR logic, a weighted average, or a wildcard match that SUMIFS won’t run. That’s the moment people who learned spreadsheets in the Excel 2007 era pull SUMPRODUCT out of the drawer, and beginners hit a wall.

SUMPRODUCT is the older, stranger workhorse: an array engine hiding inside a normal-looking function. You write it without Ctrl+Shift+Enter, you can stack conditions however you want, and you stop caring whether your Excel build supports dynamic arrays. This guide walks the multi-criteria patterns that actually come up at work — counts, sums, weighted averages — and the trade-offs against SUMIFS that most tutorials skip.

Why a multi-criteria sum is harder than it looks

SUMIFS does one job well: AND-logic conditional sums. Give it a sum range, then pairs of (criteria range, criterion), and it adds everything that matches every condition. That covers maybe 70% of real reports.

The other 30% is where the gaps show up. SUMIFS will not OR two values inside one column without trickery. It cannot weight a sum by a second column. It does not let you build a criterion out of an expression like MONTH(B2:B500)=7. And it forces every criteria range to be the same shape as the sum range — there’s no flexibility for cross-tabulating two dimensions in one shot.

SUMPRODUCT is the older alternative that handles all of those because it does not have “criteria” at all. It just multiplies arrays, and turns conditions into 1s and 0s along the way. That generality is the whole reason it’s still in every Excel power user’s toolkit a decade after SUMIFS shipped.

The takeaway: SUMIFS wins the boring cases, SUMPRODUCT wins everything else. The Microsoft Excel reference for the function lives at the Microsoft Support SUMPRODUCT page, which is the source we’ll cite when behavior depends on the documented signature.

How SUMPRODUCT actually works under the hood

The mental model that makes every other example obvious: SUMPRODUCT takes one or more arrays of the same shape, multiplies them position by position, and then adds the results. =SUMPRODUCT({1,2,3}, {10,20,30}) returns 1×10 + 2×20 + 3×30 = 140.

Multiplying numbers is the easy half. The trick is what happens when you multiply a number array by a comparison. In Excel, a comparison like A2:A6="Retail" returns an array of TRUE/FALSE values. Multiplied by anything numeric, TRUE becomes 1 and FALSE becomes 0. So =SUMPRODUCT((A2:A6="Retail")*C2:C6) sums column C only where column A says Retail.

That’s also why you see the double-negative -- in older formulas. --(A2:A6="Retail") coerces TRUE/FALSE into 1/0 before the function gets to it. When you multiply by a numeric array immediately, the coercion is automatic and you don’t need it. When you pass the comparison alone — for a pure count — the -- earns its keep.

With the data above, =SUMPRODUCT((A2:A5="Retail")*(B2:B5="West")*C2:C5) returns 1800 — rows 2 and 5 match both conditions, rows 3 and 4 collapse to zero. Once that picture clicks, the rest of this guide is permutations on the same trick.

Multi-criteria AND with one formula

The most common pattern at work: sum a number column where two or more text columns hit specific values. SUMIFS will do it, but SUMPRODUCT is one line and survives when the criteria stop being simple equality.

=SUMPRODUCT((Channel="Retail")*(Region="West")*(Quarter="Q3")*Revenue)

Each (range=value) term is an array of TRUE/FALSE for that column. Multiplying them with * means a row contributes only when every comparison is TRUE — that’s logical AND expressed as 1×1×1×n. As soon as one is FALSE, the whole row collapses to zero before the sum.

Named ranges make these readable

Long formulas with $A$2:$A$10000 repeated four times are unreadable, and that unreadability is a quiet bug source. Either convert the data range to a Table (Ctrl+T) and reference structured columns like Sales[Region], or define named ranges for each column. The formula above already uses named-range shorthand for that reason.

Cross-tabulating is the same idea: if A2:A10 holds row labels and B1:F1 holds column labels, =SUMPRODUCT((A2:A10="West")*(B1:F1="Q3")*B2:F10) picks out the cell at the intersection. SUMIFS cannot do that in one call.

OR logic with the plus sign

Switch the operator and the meaning flips. If * is AND, then + is OR — because adding TRUE+TRUE can give 2, which would double-count, you sometimes need to clamp it. For most cases, the addition is clean.

=SUMPRODUCT(((Channel="Retail")+(Channel="Wholesale"))*Revenue)

The inner expression evaluates to 1 if the row is either Retail or Wholesale, 0 otherwise — exactly the OR you want. The clamp matters when both conditions can be TRUE for the same row: (A=x)+(A=y) on a single column is safe because A can’t be x AND y, but two columns each set to “include if either” can collide. Wrap the OR with --(... > 0) if you ever see 2s in the array.

Weighted averages and other patterns

Weighted averages are where SUMPRODUCT genuinely has no competitor in the conditional-sum family. The pattern: multiply quantities by their weights, sum that, then divide by the sum of weights.

=SUMPRODUCT(Scores, Weights) / SUM(Weights)

If Scores is {85, 90, 78} and Weights is {0.5, 0.3, 0.2}, you get (85×0.5 + 90×0.3 + 78×0.2) / 1 = 85.1. The same shape powers any “rate × volume” report: revenue weighted by margin, hours weighted by billing rate, survey scores weighted by response counts.

You can layer a filter on top. =SUMPRODUCT((Region="West")*Units*Price) sums revenue (Units×Price) for West-region rows only. The structure is consistent: comparison arrays for filtering, numeric arrays for the values you actually want to multiply, all the same length.

Counting is the no-numeric-array version. =SUMPRODUCT(--(Region="West"), --(Channel="Retail")) counts rows where both columns hit. The double-negative here is doing real work — without it you’d be summing TRUE/FALSE values, which Excel happily turns into zero. For more on the family of conditional counters and how SUMIFS, COUNTIFS, and AVERAGEIFS divide the labor, see our SUMIFS vs COUNTIFS walkthrough.

SUMPRODUCT versus SUMIFS in modern Excel

If you’re on Microsoft 365 or Excel 2021+, the calculation engine handles arrays natively and dynamic-array functions like FILTER, BYROW, and SUMIFS-with-array-criteria shrink the cases that need SUMPRODUCT. The function did not become obsolete. Its niche shifted.

The honest decision tree: reach for SUMIFS when every criterion is “column equals literal” and AND across columns is enough. Reach for SUMPRODUCT when you need OR inside a column, a weighted sum, an expression-based criterion (YEAR(Date)=2026), wildcards via SEARCH, or a cross-tab calculation. On modern Excel, a third option is FILTER + SUM, which reads naturally for single-dimension filters but gets noisy fast with three or more conditions.

=SUMIFS(Revenue,Region,"West",Channel,"Retail")+SUMIFS(Revenue,Region,"East",Channel,"Retail")

=SUMPRODUCT(((Region="West")+(Region="East"))*(Channel="Retail")*Revenue)

Performance: do not feed it whole columns

SUMPRODUCT walks every row of every array you give it, every time anything in the workbook recalculates. =SUMPRODUCT((A:A="West")*C:C) on a fresh sheet processes a million-row array twice on every edit, which turns a snappy workbook into a sluggish one. Always bound the range — to the Table, the named range, or an explicit A2:A20000. SUMIFS is internally smarter about whole columns; SUMPRODUCT is not.

Fixing #VALUE! and the wildcard trap

The single most common SUMPRODUCT error is #VALUE!, and the cause is almost always the same: array dimensions don’t match. =SUMPRODUCT((A2:A100="West")*B2:B500) fails because the first array has 99 rows and the second has 499. Match the row counts — and the column counts, if either side is a row range — and the error disappears.

1. Select the formula in the formula bar.
2. Highlight one of the array expressions (e.g. A2:A100="West").
3. Press F9. Excel shows the evaluated array inline — count the TRUE/FALSE entries.
4. Do the same for the other arrays. Mismatched counts = the bug.
5. Press Esc to restore the formula (never Enter, or you commit the literal array).

Text-value rows hiding in a numeric column are the second trap. If Revenue contains the string “n/a” anywhere, *Revenue propagates #VALUE! through the whole calculation. Wrap the numeric array in IFERROR(Revenue, 0) or clean the source.

SUMPRODUCT doesn’t accept Excel’s * and ? wildcards the way SUMIFS does — that’s the documented limitation. The workaround is ISNUMBER(SEARCH(...)): SEARCH returns a position number on match and #VALUE! otherwise, and ISNUMBER turns that into the TRUE/FALSE array SUMPRODUCT expects.

=SUMPRODUCT(ISNUMBER(SEARCH("retail",Channel))*Revenue)

That formula sums revenue for every channel whose name contains “retail” anywhere — case-insensitive, partial-match, no native wildcard needed. For the broader family of array-formula patterns this kind of expression unlocks, our walkthrough of array formulas in Google Sheets uses the same conceptual building blocks even though the engine differs.

What to keep and what to leave

Use SUMIFS by default. Reach for SUMPRODUCT the day you need OR logic, a weighted sum, an expression-driven criterion, a wildcard match SUMIFS can’t do, or a cross-tab in one formula. Bound your ranges, always parenthesise the OR groups, and the #VALUE! errors will be rare. The function is older than most of the dynamic-array machinery and still earns its place in the formula bar — see our broader essential Excel formulas roundup for where it fits among the rest of the toolkit.

• ✓ Every array in the formula has the same length
• ✓ OR groups inside one column are wrapped in their own parentheses
• ✓ Ranges are bounded — no A:A, no whole-column refs
• ✓ Wildcards rewritten as ISNUMBER(SEARCH(...))
• ✓ Text-error rows in numeric columns wrapped with IFERROR