SUMIFS with Multiple Criteria: The Complete Practical Guide
Master SUMIFS in Microsoft Excel and Google Sheets. Learn how to sum data using multiple conditions, date ranges, wildcards, and dynamic criteria references with real-world examples.
SUMIFS is one of the most practical formulas in any spreadsheet analyst's toolkit. While SUMIF handles a single condition, SUMIFS lets you define as many criteria as your data requires — and all conditions must be true simultaneously for a row to be included.
Understanding the Syntax
- sum_range — The cells you want to add together.
- criteria_range1 — The first column to evaluate.
- criteria1 — The condition that must be matched in
criteria_range1. - Additional pairs can follow with no hard limit.
Note: All ranges must be the same size as
sum_range. Mismatched ranges return a#VALUE!error.
Example 1: Sum Sales by Region and Product
Imagine a sales report with columns: Date (A), Region (B), Product (C), Revenue (D).
To sum all revenue from the North region for Widget A:
This returns the total only for rows where both conditions are true.
Example 2: Dynamic Cell References Instead of Hardcoded Values
Hardcoding strings like "North" into formulas is fragile. Reference a cell instead:
Now changing G2 or H2 automatically updates the formula result. This pattern is essential for dashboard-style reporting where users change filter values.
Example 3: Date Range Criteria
SUMIFS handles date-based conditions by comparing the date serial number. Use comparison operators wrapped in quotes:
This sums revenue for Q1 2024. The & operator joins the comparison string with the date value returned by DATE().
Using cell references for dates:
Where J2 contains the start date and K2 the end date.
Example 4: Wildcard Matching
SUMIFS supports two wildcards:
*— Matches any sequence of characters?— Matches exactly one character
Sum all revenue for products starting with "Widget":
Sum products with exactly five-character names:
Example 5: NOT Equal Criteria
Exclude a specific category using <>:
This sums revenue from every region except "South".
Common Errors and Fixes
| Error | Cause | Fix |
|---|---|---|
#VALUE! | Range sizes do not match | Ensure all ranges span the same number of rows |
0 returned unexpectedly | Text stored as numbers or leading spaces | Use TRIM() on data or check cell format |
| Wrong result with dates | Date comparison using text | Use DATE() or DATEVALUE() to convert |
SUMIFS vs SUMPRODUCT for OR Logic
SUMIFS uses AND logic only. To sum where either of two criteria is true, use SUMPRODUCT:
Note that rows matching both conditions are counted twice with +. For true OR without double-counting:
Performance Tip for Large Datasets
When working with tables containing tens of thousands of rows, limit your ranges to the actual data rows rather than full column references (D:D). Full column references force Excel to evaluate over a million cells even when most are empty, slowing recalculation noticeably.
Use structured table references instead:
Structured references auto-expand when new rows are added to the table, eliminating the need for oversized ranges.