For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

SUMIFS vs SUMPRODUCT: when each formula is the right choice

FN.S2 =SUMIFS() VS =SUMPRODUCT()
SUMIFS vs SUMPRODUCT: when each formula is the right choice — Excel example preview

Use SUMIFS for fast multi-condition totals on large tables; reach for SUMPRODUCT when multiplying Price × Quantity arrays inline.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Mixed

Excel Dynamic Array Video#4: Pivot table with total row, FILTER, UNIQUE, SORT, SORT & SUMIFS

AAA Excels Power

Open on YouTube (opens in a new tab)
AAA Excels Power

Loading preview…

SUMIFS and SUMPRODUCT both add up numbers that meet conditions, and on a simple criterion they return the same answer. They are not the same tool: SUMIFS is a purpose-built aggregator with a fixed vocabulary of conditions, and SUMPRODUCT is general-purpose array arithmetic that happens to be excellent at conditional sums.

Knowing where SUMIFS runs out is most of the skill.

Start with SUMIFS

=SUMIFS(tblSales[Amount],tblSales[Region],"North",tblSales[Date],">="&$B$1)

It is readable by anyone, it is fast, and it handles the ordinary cases — equals, not equals, greater than, between, blank, not blank. Note the & when building a comparison against a cell: the operator and the value have to be concatenated into one text string, and omitting that is the usual reason a date criterion returns zero.

Wildcards work too, within limits:

=SUMIFS(tblSales[Amount],tblSales[Product],"Widget*")

* matches any sequence, ? any single character. What they cannot do is match in the middle reliably across mixed data, or test anything the criteria syntax has no words for.

Where SUMIFS runs out

Contains, case-sensitively or against a cell reference. "*"&D2&"*" covers simple contains, but as soon as you need case sensitivity, SUMIFS has nothing:

=SUMPRODUCT((ISNUMBER(FIND("Widget",tblSales[Product])))*tblSales[Amount])

FIND is case-sensitive; SEARCH is not. Swap them to choose.

OR across values in one column. SUMIFS treats multiple criteria as AND. For OR, you either add two SUMIFS together or use SUMPRODUCT:

=SUMPRODUCT(((tblSales[Region]="North")+(tblSales[Region]="South"))*tblSales[Amount])

The + is OR; * is AND. There is a neater SUMIFS trick for this specific case:

=SUM(SUMIFS(tblSales[Amount],tblSales[Region],{"North","South"}))

The array constant makes SUMIFS return one result per value, and SUM adds them. That is faster and more readable than SUMPRODUCT — but it only works for a list of literal values, not for conditions.

Criteria derived from a calculation. This is the big one. SUMIFS can only test the values in a column. It cannot test something computed from them:

=SUMPRODUCT((MONTH(tblSales[Date])=3)*tblSales[Amount])
=SUMPRODUCT((WEEKDAY(tblSales[Date],2)>5)*tblSales[Amount])
=SUMPRODUCT((LEN(tblSales[Code])<>8)*tblSales[Amount])

Month, weekday, string length, a calculation on two other columns — none of these are expressible in SUMIFS. You can always add a helper column and then use SUMIFS, and for a recurring report that is usually the better answer: a PeriodKey column of =TEXT([@Date],"yyyy-mm") makes monthly SUMIFS trivial and faster than any array formula.

Weighted sums. This is what SUMPRODUCT was actually designed for:

=SUMPRODUCT(tblSales[Qty],tblSales[UnitPrice])

Multiply two columns row by row and total the result. SUMIFS cannot do this at all, and the alternative is a helper column you may not want.

Add a condition by multiplying in a third array:

=SUMPRODUCT((tblSales[Region]="North")*tblSales[Qty]*tblSales[UnitPrice])

Comparing two columns to each other:

=SUMPRODUCT((tblSales[Actual]>tblSales[Target])*tblSales[Amount])

Two things that will bite you with SUMPRODUCT

Text in a numeric range breaks it. SUMPRODUCT returns #VALUE! if any cell in a multiplied array is text, where SUMIFS would ignore it. Wrap the value array in N() or clean the column.

The ranges must be the same size. Mismatched ranges give #VALUE!. Table column references make this automatic; raw addresses do not, and a range that is one row shorter than its neighbour is a classic cause.

A third thing worth knowing: the -- you see in older SUMPRODUCT formulas (the “double unary”) converts TRUE/FALSE into 1/0. Multiplying arrays together with * does the same coercion, so -- is only needed when you are adding conditions rather than multiplying — SUMPRODUCT(--(A2:A100="North")) to count, for instance.

Performance

On a few thousand rows, neither is noticeably faster. Past roughly fifty thousand, the gap becomes real.

SUMIFS is a native aggregation that Excel optimises, including multi-threading across cores. SUMPRODUCT builds full in-memory arrays for every argument and evaluates every row regardless of the criteria. A sheet with a few hundred SUMPRODUCTs over whole-column references is the classic cause of a workbook that takes six seconds to recalculate.

Two mitigations that matter more than the function choice: never use whole-column references (A:A makes Excel consider 1,048,576 rows), and add a helper column so SUMIFS can do the work.

What FILTER changes

On Microsoft 365, a third option exists and is often the most readable:

=SUM(FILTER(tblSales[Amount],(MONTH(tblSales[Date])=3)*(tblSales[Region]="North"),0))

FILTER takes the same array-logic conditions as SUMPRODUCT but reads as what it is — “sum the amounts where these things are true”. The final 0 is the if-empty value, and omitting it gives #CALC! when nothing matches.

It is also genuinely useful during debugging: drop the SUM and you can see exactly which rows the condition is selecting, which is much harder with SUMPRODUCT.

Quick rule

  1. SUMIFS for anything it can express. Readable, fast, understood by everyone.
  2. A helper column, then SUMIFS, when the criterion is calculable and the report recurs. This is the under-used answer.
  3. SUMPRODUCT for weighted sums, column-to-column comparisons, and criteria you cannot precompute.
  4. FILTER on modern Excel when the logic is complex and readability matters more than the last millisecond.

The SUMIFS and SUMPRODUCT reference pages have the full argument detail, and the SUMIFS/COUNTIFS practice dataset has the mixed-type and mismatched-range traps already set up. For the related question of whether a pivot table would beat either, pivot tables vs formulas.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Write formulas that are correct, readable, and resilient when the workbook changes. For this article, the goal is to practice: Where SUMIFS runs out — partial matches, OR logic, calculated criteria, and weighted sums — what SUMPRODUCT costs you in speed and readability, and what FILTER changes on modern Excel.

Practice workbook setup

Create a small table with clean headers, sample edge cases, and expected outputs you can check manually.

Practice workflow

  1. Write the business rule in plain English before writing the formula.
  2. Build the formula in small pieces and test each piece.
  3. Use absolute references, table references, or named ranges intentionally.
  4. Test blanks, missing values, duplicate matches, and unexpected text.

Quality checks

  • The formula returns the expected result for normal and edge-case rows.
  • References point to stable ranges or tables.
  • Someone else can understand the formula after reading the labels around it.

Common mistakes

  • Fixing one row without testing the rest of the column.
  • Using approximate match or partial criteria without meaning to.
  • Letting hidden spaces or text numbers cause false mismatches.

Next actions

  • Create three test rows that should return different outcomes.
  • Compare the result with an alternate formula or manual filter.

Formula focus: test SUMIFS(), SUMPRODUCT(), COUNTIFS(), FILTER(), SUM() with normal rows, blank inputs, and at least one edge case.