The SUMIFS with multiple criteria formula is one of the most powerful tools in all of Excel. It lets you sum values that match two, three, or even ten conditions simultaneously — across text, numbers, dates, and wildcards. In this guide, you will find 8 real-world examples that go well beyond the basics, each paired with a sample dataset and a formula you can copy directly into your spreadsheet.
SUMIFS Syntax Explained
Before working through the examples, here is the full SUMIFS syntax so every argument is completely clear:
| Argument | Required? | What it means |
|---|---|---|
| sum_range | Required | The cells containing the numbers you want to add up |
| criteria_range1 | Required | The first column to evaluate against a condition |
| criteria1 | Required | The first condition — text, number, date, or comparison expression |
| criteria_range2, criteria2 | Optional | Additional column + condition pairs. Up to 127 pairs total. |
sum_range as the first argument. SUMIF places it last. Also, SUMIFS applies AND logic by default — every condition must be true for a row to contribute to the sum.
Example 1: Two Text Criteria (Region + Product)
The most common use of SUMIFS with multiple criteria is matching two text columns. Here you want to sum sales for the North region and the Laptop product only.
Rows 2, 3, and 5 are excluded because each fails at least one condition. This is the AND logic SUMIFS applies automatically — a row must satisfy every criterion to be counted.
Example 2: Sum Between a Date Range
SUMIFS handles date ranges using comparison operators. This is ideal for monthly or quarterly reporting where you need to isolate a specific time window.
">="&E1. Never put the cell reference inside the quotes — it will be treated as literal text and return zero.
Example 3: Greater Than / Less Than Numbers
Numeric comparisons let you filter rows where a value falls above or below a threshold before summing. This is common in payroll, inventory, and sales analysis.
Example 4: Wildcard Matching with *
SUMIFS supports the wildcard characters * (any sequence of characters) and ? (exactly one character). This lets you match partial text — perfect for grouping product families, sub-regions, or order codes that share a prefix.
"North*" — starts with North |
"*Pro*" — contains "Pro" anywhere |
"??-2026" — exactly two chars then -2026 |
"~*" — literal asterisk (tilde escapes)
Example 5: Three Conditions Combined
SUMIFS scales effortlessly to three or more conditions. Here you filter by salesperson, region, and product simultaneously — a scenario common in sales commission reporting.
Example 6: OR Logic (Two SUMIFS Added Together)
Since SUMIFS uses AND logic natively, OR logic requires a workaround: add two separate SUMIFS results together. This gives the total where condition A or condition B is true.
Example 7: Dynamic Criteria from a Cell
Hard-coding criteria works for one-off analysis, but for dashboards and reports you should reference cells. This way a user changes the filter value in one cell and every connected SUMIFS updates automatically.
Example 8: Summing Across Multiple Sheets
A common quarterly reporting pattern uses one sheet per month. The cleanest approach is a SUMIFS per sheet added together, or better still, consolidate all data on a single master sheet with a Month column.
Quick-Reference: All Comparison Operators in SUMIFS
| Operator syntax | Meaning | Example formula |
|---|---|---|
| "="&A1 | Equal to cell A1 | =SUMIFS(C:C, A:A, "="&A1) |
| "<>"&A1 | Not equal to cell A1 | =SUMIFS(C:C, A:A, "<>"&A1) |
| ">"&A1 | Greater than A1 | =SUMIFS(C:C, B:B, ">"&A1) |
| ">="&A1 | Greater than or equal to A1 | =SUMIFS(C:C, B:B, ">="&A1) |
| "<"&A1 | Less than A1 | =SUMIFS(C:C, B:B, "<"&A1) |
| "<="&A1 | Less than or equal to A1 | =SUMIFS(C:C, B:B, "<="&A1) |
| "<>" | Not blank / not empty | =SUMIFS(C:C, A:A, "<>") |
| "" | Is blank / empty | =SUMIFS(C:C, A:A, "") |
| "North*" | Starts with "North" (wildcard) | =SUMIFS(C:C, A:A, "North*") |
| "*Pro*" | Contains "Pro" anywhere | =SUMIFS(C:C, A:A, "*Pro*") |
Troubleshooting: SUMIFS Returning Zero or Wrong Results
If your SUMIFS returns 0 when you expect a number, or the wrong number entirely, work through these five common causes:
1. Range sizes don't match
Every criteria_range must be exactly the same size as the sum_range. If sum_range is C2:C100, every criteria_range must also be 99 rows tall. Mismatched sizes silently return zero.
2. Numbers stored as text
If your criteria column holds numbers formatted as text (look for the green triangle in the corner of each cell), SUMIFS will not match them against numeric criteria. Fix: select the column → Data → Text to Columns → Finish.
3. Extra spaces in cell values
A cell containing "North " (trailing space) will not match "North". Clean data first with TRIM(), or use a wildcard as a workaround: "North*".
4. Date format mismatch
Dates in your sheet must be genuine Excel serial dates (stored as numbers internally), not text that looks like dates. Press Ctrl+` to toggle formula view — real Excel dates display as 5-digit numbers like 46017.
5. Operator placed outside the quote marks
This is wrong: =SUMIFS(C:C, B:B, >"&A1). The operator must be inside the quotes and joined with &: =SUMIFS(C:C, B:B, ">"&A1).
=SUMIFS(C:C, A:A, >=10) without quotes causes an error. Operators must always be inside quote marks, whether hard-coded or concatenated with a cell reference.
Frequently Asked Questions
-
What is the difference between SUMIF and SUMIFS in Excel?SUMIF supports only one condition, while SUMIFS supports up to 127 criteria pairs, making it far more powerful for multi-condition sum operations. SUMIFS also places
sum_rangeas the first argument, whereas SUMIF places it last — a subtle but important syntax difference. -
Can SUMIFS use OR logic across multiple criteria?Yes. Add two SUMIFS results together (
=SUMIFS(...) + SUMIFS(...)) to apply OR logic, since SUMIFS natively uses AND logic between conditions. For multiple OR values, use SUMPRODUCT:=SUMPRODUCT(SUMIFS(sum_range, criteria_range, {"A","B","C"})). -
Can SUMIFS handle date ranges?Yes. Use comparison operators inside quotes and concatenate cell references with &:
=SUMIFS(B:B, A:A, ">="&E1, A:A, "<="&E2). Make sure dates in your column are real Excel serial dates and not text-formatted strings — press Ctrl+` to verify. -
Does SUMIFS work with wildcards?Yes. Use
*for any sequence of characters and?for a single character. For example,"North*"matches North, Northern, North-East, etc. To match a literal asterisk, prefix it with a tilde:"~*". -
Why is my SUMIFS returning zero?Common causes: criteria ranges and sum_range have different sizes, numbers stored as text, extra spaces in cell values, date format mismatches, or a comparison operator placed outside quote marks. See the troubleshooting section above for step-by-step fixes for each issue.
-
How many criteria can SUMIFS handle?SUMIFS supports up to 127 criteria pairs (each pair is one criteria_range and one criteria argument). Performance remains fast for datasets up to several hundred thousand rows in modern Excel and Microsoft 365.