The Excel SUMIFS function, explained interactively.
SUMIFS sums values that pass every one of multiple conditions. It’s SUMIF with AND logic, but with one crucial twist: the sum_range argument goes first — opposite of SUMIF. Change the criteria below and watch which rows survive the filter.
How to use SUMIFS
Pick a category and a month — only the rows matching both conditions contribute to the sum. Rows that fail either condition stay dimmed.
A2:A8) against a value like "Food" or a comparison like "<>Food".| A | B | C | |
|---|---|---|---|
| 1 | Category | Month | Amount |
| 2 | Food | Jan | 12.40 |
| 3 | Travel | Jan | 88.00 |
| 4 | Food | Feb | 6.75 |
| 5 | Office | Jan | 21.30 |
| 6 | Travel | Feb | 42.00 |
| 7 | Food | Feb | 9.10 |
| 8 | Office | Feb | 15.20 |
| 9 | |||
| 10 | SUMIFS result | 12.40 | |
SUMIFS syntax and arguments
One sum_range followed by up to 127 pairs of criteria_range and criteria. See Microsoft’s official SUMIFS reference for the canonical specification.
sum_range and every subsequent criteria_range.criteria_range1 must match. Accepts exact values ("Food"), comparisons (">=50"), wildcards ("*berry"), and cell references. Operators must stay inside the quotes; concatenate with & to inject a cell value: ">"&E1.criteria_range + criteria. Each pair is ANDed with every other — a row is summed only when it matches all criteria.criteria1. Pairs can test different columns or different sheets.SUMIFS examples
Four patterns that cover 90% of real-world SUMIFS use.
Example 1: SUMIFS with two exact matches
The canonical case — total an expense category within a specific month.
Sums column C where column A is Food AND column B is Jan. Both conditions must pass.
Example 2: Converting a SUMIF to SUMIFS
When a client asks for an extra condition on top of an existing SUMIF, don’t just add an argument — you have to reshuffle.
Notice C2:C100 (the sum range) jumps from last to first. Every other argument stays in its (range, criteria) pair. Miss this and you’ll get #VALUE! or a silently wrong total.
Example 3: SUMIFS with a comparison operator
Multi-column filter plus a numeric threshold — e.g. big Food spends in any month.
Operators go inside the quotes: ">=20", not >=20. Here C2:C100 appears twice — once as sum_range, once as a criteria_range — which is perfectly legal.
Example 4: SUMIFS with dynamic criteria from cells
Build a dashboard where filter values come from cells the user can change.
E1 holds the category name (used directly); E2 holds a numeric threshold, concatenated onto the operator with &. Ideal for dashboard-driven reports — change the inputs, watch the total recompute.
Common SUMIFS errors and fixes
Four failure modes, each with what to check and how to recover.
SUMIFS returns #VALUE!
Cause: ranges in the formula have different sizes. sum_range and every criteria_range must span the same number of cells.
Align the ranges — if sum_range is C2:C100, every criteria_range must also be 99 cells tall.
SUMIFS returns 0 unexpectedly
Cause: no row passes all criteria simultaneously (remember, AND not OR), or sum_range points at text.
Drop one criterion at a time and re-check — you’ll find which pair is too strict. For OR logic, add two SUMIFS calls or use SUMPRODUCT.
SUMIFS argument order mistake
Cause: converting a SUMIF formula without moving sum_range to the front.
Rewrite: SUMIF has sum_range last, SUMIFS has it first. If in doubt, delete the formula and retype it — muscle memory is how this bug survives.
SUMIFS criteria: operator outside the quotes
Cause: wrote SUMIFS(C:C, A:A, Food, B:B, >20) instead of "Food" and ">20".
Quote both the literal text and the comparison. For a cell ref with an operator, concatenate: ">"&E2.
SUMIFS vs SUMIF, COUNTIFS & AVERAGEIFS
The -IFS family applies the same multi-criteria pattern to three aggregates: sum, count, average.
| Function | Criteria | Aggregates | Argument order |
|---|---|---|---|
| SUMIFS | One or many | Sum | sum_range, range1, crit1, range2, crit2, … |
| SUMIF | Exactly one | Sum | range, criteria, [sum_range] |
| COUNTIFS | One or many | Count | range1, crit1, range2, crit2, … |
| AVERAGEIFS | One or many | Average | average_range, range1, crit1, … |
Note the pattern: SUMIFS and AVERAGEIFS put the aggregated range first; COUNTIFS doesn’t have an aggregated range (it just counts matching rows) so it starts with the first range/criteria pair. If you need a single condition, SUMIF reads more naturally — but once you add a second filter, SUMIFS is non-negotiable.
SUMIFS frequently asked questions
6.01What’s the difference between SUMIF and SUMIFS?▸
SUMIF supports exactly one condition; SUMIFS supports up to 127. SUMIFS also flips the argument order — sum_range goes first, whereas in SUMIF it’s last. SUMIFS is strictly more powerful, so some style guides recommend using it exclusively even for single-condition sums.
6.02Why does the argument order change between SUMIF and SUMIFS?▸
Historical design decision. SUMIFS takes a variable number of (range, criteria) pairs, and sum_range must sit outside that repeating group — so Microsoft put it first. Most conversion bugs between SUMIF and SUMIFS come from forgetting this flip.
6.03Is SUMIFS AND or OR logic?▸
AND. Every row must pass every condition to be summed. For OR logic, add two SUMIFS expressions together or switch to SUMPRODUCT: =SUMIFS(…, A:A, "Food") + SUMIFS(…, A:A, "Travel").
6.04Can SUMIFS use comparison operators like >=50?▸
Yes — just keep the operator inside quotes: =SUMIFS(C:C, A:A, "Food", B:B, ">=50"). A bare >=50 without quotes is a syntax error. To combine an operator with a cell reference, concatenate with &: ">="&E2.
6.05How many criteria can SUMIFS take?▸
Up to 127 (range, criteria) pairs, though you rarely need more than a handful. Readability usually suffers above 3-4 criteria; consider a helper column or a pivot table instead.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.