Excel SUMIFS Function (Sum With Multiple Conditions) – Examples & Practice
Practice the Excel SUMIFS function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Sum values in C2:C6 for rows where A2:A6 is "South" and B2:B6 is "Paul".
Formula Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
- sum_range: The range of cells to sum.
- criteria_range1: The first range to evaluate.
- criteria1: The criterion applied to criteria_range1.
- ...: Additional criteria_range / criteria pairs.
What it does
SUMIFS sums values using one or more criteria ranges. It is the standard tool for multi-condition totals on flat tables.
Excel SUMIFS Function Examples
Two conditions
=SUMIFS(C2:C6, A2:A6, "South", B2:B6, "Paul")
Sums C where A is South and B is Paul.
sales-by-region.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Liam | 320 |
| 3 | South | Mia | 280 |
| 4 | North | Noah | 410 |
| 5 | West | Olivia | 295 |
| 6 | South | Paul | 360 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Name ranges when the same pairs repeat across a workbook.
- Document AND vs OR logic for stakeholders.
- Prefer tables (Insert Table) for self-describing columns.
SUMIFS Function Use Cases
- Multi-filter revenue totals
- Rep + region reporting
- Budget vs actual slices
- Operational KPIs with several dimensions
- Layered criteria dashboards
Common mistakes - SUMIFS function not working
- Reversing sum_range and criteria_range order vs SUMIF
- Unequal range sizes across pairs
- Embedding OR logic incorrectly
- Criteria referencing whole columns inconsistently
- Using SUMIFS for DISTINCT sums without a model for uniqueness
FAQ
What is the argument order?
SUMIFS starts with sum_range, then pairs of criteria_range and criteria.
SUMIFS vs SUMPRODUCT?
SUMIFS is clearer for many conditional sums; SUMPRODUCT is flexible for advanced array math.
Can I use date criteria?
Yes, with proper serial comparisons or DATE functions.
Why #VALUE!?
Often caused by mismatched range shapes or invalid criteria expressions.
Does SUMIFS support OR logic?
Not directly; use multiple SUMIFS added together or helper columns for OR patterns.
Comparison
| Function | Criteria count |
|---|---|
| SUMIFS | One or more |
| SUMIF | One |
| DSUM | Database-style (legacy) |
Example
=SUMIFS(C2:C6, A2:A6, "South", B2:B6, "Paul")
Advanced examples
SUMIFS + dynamic months
Pair criteria cells with EOMONTH/DATE patterns to roll periods without rewriting formulas.
Related functions
SUMIF stays the readable choice for exactly one test; SUMIFS is the workhorse for multi-column filters.
Align with COUNTIFS and AVERAGEIFS so every metric on a dashboard shares one documented criteria block.