SUMIFS vs SUMPRODUCT: when each formula is the right choice

Use SUMIFS for fast multi-condition totals on large tables; reach for SUMPRODUCT when multiplying Price × Quantity arrays inline.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)
Leila Gharani
Open on YouTube (opens in a new tab)
How to Use IF, IFS, COUNTIFS, SUMIFS, IFERROR, and IFNA in Excel
Simon Sez IT
Open on YouTube (opens in a new tab)
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)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
- SUMIFS for anything it can express. Readable, fast, understood by everyone.
- A helper column, then SUMIFS, when the criterion is calculable and the report recurs. This is the under-used answer.
- SUMPRODUCT for weighted sums, column-to-column comparisons, and criteria you cannot precompute.
- 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.
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
- Write the business rule in plain English before writing the formula.
- Build the formula in small pieces and test each piece.
- Use absolute references, table references, or named ranges intentionally.
- 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.






