How to Use SUMIFS with Multiple Criteria in Excel
SUMIFS adds values that meet multiple conditions — a fundamental skill for reporting, dashboards, and any spreadsheet where you're aggregating "totals where X and Y and Z". Once you get past the argument order and the operator syntax, it becomes one of Excel's most reliable workhorses.
What SUMIFS does
SUMIFS sums the values in a range where every specified condition is true. Every criteria pair is combined with AND — a row contributes only if all conditions match.
Syntax
| Argument | Description |
|---|---|
sum_range Required | The range of values to sum. Only rows where all criteria match contribute. |
criteria_range1 Required | The first range to test. |
criteria1 Required | The condition to test criteria_range1 against. |
criteria_range2, criteria2, ... Optional | Additional range/condition pairs. Up to 127 pairs. |
SUMIF puts the sum range last. SUMIFS puts it first. This trips up almost everyone switching between them. When in doubt, remember: SUMIFS starts with what you're summing.
Basic examples
Single criterion
=SUMIFS(Sales[Amount], Sales[Region], "West")Two criteria (AND)
=SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Product], "Widget")Three criteria
=SUMIFS(Sales[Amount],
Sales[Region], "West",
Sales[Product], "Widget",
Sales[Quarter], "Q3")Using operators in criteria
Beyond exact match, criteria can include comparison operators. Wrap them in quotes.
Greater than
=SUMIFS(Sales[Amount], Sales[Amount], ">1000")Not equal to
=SUMIFS(Sales[Amount], Sales[Region], "<>West")Between (two criteria on the same range)
=SUMIFS(Sales[Amount], Sales[Amount], ">=100", Sales[Amount], "<=1000")Referencing a cell in criteria (the & operator)
Hardcoded criteria are fragile. Reference cells for thresholds and dates that might change. The trick: use & to concatenate the operator string with the cell reference.
Greater than a cell value
=SUMIFS(Sales[Amount], Sales[Amount], ">"&A1)Date range from cells
=SUMIFS(Sales[Amount],
Sales[Date], ">="&A1,
Sales[Date], "<="&B1)Writing ">=A1" as a literal string sums nothing. Excel treats the whole thing as text and looks for dates literally equal to the string ">=A1". Use ">="&A1 — quotes around the operator, & joining the cell reference.
Wildcards for partial text matching
Two wildcards work inside criteria strings:
*— matches any sequence of characters (including none)?— matches exactly one character
Text starting with a prefix
=SUMIFS(Sales[Amount], Sales[Product], "Widget*")Text containing a substring
=SUMIFS(Sales[Amount], Sales[Notes], "*urgent*")Product codes with specific pattern
=SUMIFS(Sales[Amount], Sales[Code], "A?B*")Common patterns
1. Sales by region and quarter
West region, Q3 total
=SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Quarter], "Q3")2. Year-to-date total
Everything so far this year
=SUMIFS(Sales[Amount],
Sales[Date], ">="&DATE(YEAR(TODAY()), 1, 1),
Sales[Date], "<="&TODAY())3. Last 30 days
Rolling window
=SUMIFS(Sales[Amount], Sales[Date], ">="&(TODAY()-30))4. Exclude a specific value
Everything except cancelled orders
=SUMIFS(Sales[Amount], Sales[Status], "<>Cancelled")5. Match against a dropdown cell
Dashboard-driven total
=SUMIFS(Sales[Amount], Sales[Region], D1, Sales[Quarter], D2)6. Blank-cell criterion
Rows missing a value
=SUMIFS(Sales[Amount], Sales[Category], "")7. Two-column condition using a helper
SUMIFS can't check ColumnA = ColumnB directly. Workaround: add a helper column with the comparison, then use SUMIFS on it.
Helper approach
Helper column: =IF(Sales[Actual] > Sales[Target], "Over", "Under")
Then: =SUMIFS(Sales[Amount], Sales[Helper], "Over")OR logic — the SUMIFS blind spot
SUMIFS combines criteria with AND. For OR, you have three options:
Option 1: Sum multiple SUMIFS
West or East
=SUMIFS(Sales[Amount], Sales[Region], "West") +
SUMIFS(Sales[Amount], Sales[Region], "East")Clear and readable. Fine for 2-3 alternatives.
Option 2: SUM with an array constant
Compact OR
=SUM(SUMIFS(Sales[Amount], Sales[Region], {"West","East","North"}))Passes an array of values as criteria. SUMIFS returns an array of results, SUM totals them. Great for many alternatives.
Option 3: SUMPRODUCT for complex OR
OR across different columns
=SUMPRODUCT((Sales[Region]="West") * ((Sales[Product]="Widget") + (Sales[Product]="Gadget")) * Sales[Amount])Boolean multiplication for AND, addition for OR. Powerful but harder to read.
SUMIFS vs. SUMIF
| Feature | SUMIF | SUMIFS |
|---|---|---|
| Criteria supported | 1 | 1 to 127 |
| Argument order | range, criteria, [sum_range] | sum_range, range1, criteria1, ... |
| Sum range required | Optional (if same as criteria range) | Required |
| Introduced | Excel 2003 and earlier | Excel 2007+ |
Rule: use SUMIFS by default. It handles single-criterion cases just fine and scales cleanly when you add more.
Common pitfalls
Every criteria range must have the same number of rows as sum_range. SUMIFS(A2:A100, B2:B50, "X") returns #VALUE! because A has 99 rows and B has 49. When using tables (like Sales[Amount]) this is automatic; with raw ranges, verify manually.
If the sum column has numbers but they're formatted as text (left-aligned, green triangles in corners), SUMIFS treats them as 0. Fix by re-entering, or use =VALUE() in a helper column, or Text → Columns to convert.
SUMIFS is case-insensitive. "West", "west", and "WEST" all match "West" criteria. For case-sensitive sums, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(Sales[Region], "West")) * Sales[Amount]).
SUMIFS(A:A, B:B, "X") makes Excel check every cell in the columns — over a million rows each. In large files, this drags performance. Use bounded ranges (A2:A10000) or convert to a Table.
Wildcards (*, ?) only match against text. SUMIFS(A, B, "1*") matches text "1234" but not numeric 1234. To pattern-match against numbers, convert the criteria range to text first.
Tables (Ctrl+T) auto-expand as rows are added, and structured references (Sales[Amount]) are self-documenting. SUMIFS against a Table automatically covers new rows on refresh.
Modern alternatives
For Excel 365 / 2021+, newer functions can sometimes replace SUMIFS:
- FILTER + SUM — for complex conditions returning arrays
- SUMPRODUCT — for OR logic and cross-column comparisons
- LAMBDA — to define your own aggregation logic
But SUMIFS remains the workhorse for the common case: sum where multiple conditions are true. It's faster and clearer than array formulas for straightforward filtering.
SUMIFS without the operator gymnastics
The & concatenation, wildcard rules, and OR workarounds trip up almost everyone. Excel Wizard takes plain-English intent — "total sales in West region for the last 30 days" — and writes the correct SUMIFS with cell references, date math, and proper operator quoting.
Install Excel Wizard →Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one criterion; SUMIFS takes up to 127. The argument order also differs — SUMIFS puts sum_range first. Use SUMIFS as the default even for single conditions.
Can SUMIFS use OR logic?
Not directly. Options: sum multiple SUMIFS, use SUM with an array constant ({"West","East"}), or use SUMPRODUCT for complex OR across columns.
How do I use SUMIFS with a date range?
Two criteria on the date column — one for the lower bound with ">="&startDate, one for the upper with "<="&endDate. The & is essential.
Why is my SUMIFS returning 0?
Usually: mismatched range sizes, numbers stored as text, missing & when concatenating operators with cells, or values that don't actually match (check whitespace and capitalization). Verify by narrowing to one criterion at a time.