SUMIFS
Sum values that meet multiple conditions. The workhorse of business Excel — every report showing "revenue by region for Q3" was probably built with SUMIFS. Master this one function and you can handle 80% of business aggregations.
Adds up values in sum_range that meet ALL the specified criteria. Handles up to 127 criteria pairs — enough for any realistic business filter.
What SUMIFS does
SUMIFS sums numbers that meet multiple conditions simultaneously. Given a data table with sales records — date, region, product, salesperson, revenue — you can ask "sum revenue where region is West AND product is Widget AND date is in Q3 2026". SUMIFS filters the rows matching ALL your criteria, then sums the values in your chosen column.
The reason SUMIFS dominates business Excel is that most real-world questions have multiple filters. "Total spent on marketing for the East region this quarter" involves three criteria — category, region, quarter. SUMIFS handles all of them in one clean formula, without helper columns or PivotTables.
💡 Always use SUMIFS, even for one criterion
Excel has both SUMIF (one criterion) and SUMIFS (multiple criteria). Use SUMIFS by default even when you only have one criterion. Consistent syntax makes formulas easier to read, and when you inevitably add a second criterion later, you don't have to rewrite the formula — just add another argument pair.
Syntax breakdown
Note the argument order — SUMIFS puts sum_range FIRST, which is the opposite of SUMIF (where sum_range comes last). This trips up users switching from SUMIF.
The range of cells to sum. The column of numbers you actually want totaled. Must be the same size as each criteria_range.
The range to check against criteria1. If filtering by region, this is the range containing region names.
The condition applied to criteria_range1. Can be a value ("West", 100), a comparison (">100", "<=DATE(2026,3,31)"), or a wildcard pattern ("West*"). Text and comparisons need quotes; cell references don't.
Additional range/criteria pairs, up to 127 pairs. Each pair adds another filter — all must match (AND logic, not OR).
5 real-world examples
Total revenue for the West region
Sales data with regions in column A and revenue in column D. Sum revenue only where region equals "West".
The simplest SUMIFS — one criterion. Text criteria go in quotes.
Revenue for West region AND product = Widget
Same data with product in column B. Sum revenue only where both conditions match.
Add as many range/criteria pairs as needed. All must match for a row to be included (AND logic).
Revenue for orders over $1,000
Sum revenue only for rows where the amount exceeds $1,000. The comparison operator goes inside the criteria string.
criteria_range and sum_range are the same here. That's fine — SUMIFS handles it correctly.
Revenue between two dates
Sum revenue for orders in Q3 2026 (July 1 through September 30). Two date criteria on the same date column.
Concatenate the operator with the date function using &. This pattern works for any comparison against a computed value.
Dynamic dashboard criteria
Store criteria in visible cells so users can change them without editing formulas. Region name in F2, product in G2.
Foundation for every interactive dashboard. Combine with data validation dropdowns for a polished user experience.
Common errors and how to fix them
SUMIFS returns 0 when data clearly matches
Most common cause: type mismatch. Your criteria is a number but the range contains text (or vice versa). Second common cause: trailing spaces in the criteria range.
sum_range and criteria_range have different sizes
sum_range is 100 rows but criteria_range1 is 99 rows. All ranges in SUMIFS must have identical dimensions.
Wildcard characters causing false matches
Your criteria "Sales*" matched more than expected because * is a wildcard. "Sales-North" and "Salesforce" both match.
Argument order confused with SUMIF
SUMIF's argument order is: criteria_range, criteria, sum_range. SUMIFS is: sum_range, criteria_range, criteria. Users switching from SUMIF often put arguments in the wrong order.
Blank criteria treated as "match everything"
If your criteria cell is blank and you use =SUMIFS(D:D, A:A, F2), SUMIFS treats empty F2 as matching only blank rows, not all rows.
For more, see our Function-Specific Errors guide.
SUMIFS vs SUMIF vs SUMPRODUCT vs Pivot Table
Version compatibility
Related functions
📥 Download the practice workbook
All 5 examples above in a working .xlsx file with 500 rows of sample sales data.
Frequently asked questions
Why is my SUMIFS returning 0 when I can see matching data?
99% of the time it's a data type mismatch. Your criteria is the number 5 but the range contains the text "5" (or vice versa). Common with CSV imports. Test with COUNTIFS on the same range — if it also returns 0, the data type is the issue. Fix with =VALUE() or Data → Text to Columns → Finish.
Can SUMIFS use wildcards?
Yes. Use * for any characters and ? for one character. Example: =SUMIFS(D:D, A:A, "West*") matches "West", "West Coast", "Western Region", etc. To match a literal asterisk or question mark, escape with tilde: "~*".
How do I sum by date range?
Use two criteria on the same date column with >= and <=: =SUMIFS(amount, date_col, ">="&start_date, date_col, "<="&end_date). Concatenate operators with cell references or DATE() using &.
Should I use whole-column references like A:A?
In modern Excel, generally safe — Excel is smart about ignoring empty rows. Best practice is to convert your data to an Excel Table and use structured references (Sales[Revenue]) which auto-expand as data grows. For huge datasets, use explicit ranges for performance.
How do I do OR logic in SUMIFS?
You can't directly — SUMIFS is AND-only. For OR, add multiple SUMIFS together: =SUMIFS(D:D, A:A, "West") + SUMIFS(D:D, A:A, "East"). Or use SUMPRODUCT with addition: =SUMPRODUCT((A:A="West")+(A:A="East"), D:D).
Is SUMIFS slow on large datasets?
SUMIFS is well-optimized and handles hundreds of thousands of rows fine. Where it slows down is when you have many SUMIFS formulas (100+) each scanning a large table. For heavy aggregation on big data, consider PivotTables, Power Query, or Power Pivot.
How does SUMIFS work with Excel Tables?
Excellent. Use structured references: =SUMIFS(Sales[Revenue], Sales[Region], "West", Sales[Product], "Widget"). Auto-expands as new rows are added. Self-documenting. This is the gold standard for business Excel.
Never write a broken SUMIFS again.
Describe what you want to sum — "revenue by region excluding refunds in Q3" — and the Add-in writes the exact SUMIFS formula, ranges and all.
Get the Add-in →