SUMIFS in Excel: Sum With Multiple Criteria (Examples)
How SUMIFS adds values that meet several conditions: category and month, date ranges, between two numbers, and building a summary table with locked references.
By Ayoub Farid · · 2 min read
SUMIFS adds numbers where every condition is true. Sales of Coffee in March. Hours on Project X by Sam. Expenses between two dates. If you only learn one conditional formula, make it this one, because it also handles single conditions.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Note: the sum_range comes first (the opposite of SUMIF).
Example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Region | Product | Sales |
| 2 | 03/01/2026 | North | Coffee | 120 |
| 3 | 03/01/2026 | South | Coffee | 90 |
| 4 | 03/02/2026 | North | Tea | 60 |
| 5 | 03/02/2026 | North | Coffee | 140 |
North Coffee sales:
=SUMIFS(D2:D5,B2:B5,"North",C2:C5,"Coffee") → 260Date ranges
With a start date in G1 and end date in G2:
=SUMIFS(D2:D500,A2:A500,">="&G1,A2:A500,"<="&G2)Between two values
=SUMIFS(D2:D500,D2:D500,">=100",D2:D500,"<200")A summary table that fills both ways
Regions down F2:F4, products across G1:I1. In G2:
=SUMIFS($D$2:$D$500,$B$2:$B$500,$F2,$C$2:$C$500,G$1)- Data ranges fully locked (
$D$2:$D$500) $F2locks the column (regions are always in F)G$1locks the row (products are always in row 1)
Fill right and down and every cell is correct. This exact pattern appears in job tests. See mixed references.
OR conditions
SUMIFS is AND-only. For North or South:
=SUM(SUMIFS(D2:D500,B2:B500,{"North","South"}))Common mistakes
- sum_range in the wrong place (copying SUMIF's order)
- Ranges of different sizes →
#VALUE! - Dates stored as text don't match date conditions
- **Forgetting
&** when the condition uses a cell:">="&G1, not">=G1"
Practise it
Count and sum by condition is one of the units coming to Cellio. Our Excel test questions include SUMIFS tasks you can try now.
Frequently asked questions
How do I sum with multiple criteria in Excel?
Use SUMIFS: =SUMIFS(sum_range, range1, criteria1, range2, criteria2).
Can SUMIFS handle a single condition?
Yes. =SUMIFS(D:D,B:B,"North") works just like SUMIF.
How do I use SUMIFS with dates?
Use two conditions on the date column: ">="&start and "<="&end.
Why is my SUMIFS returning 0?
Usually text that doesn't match exactly (extra spaces), numbers stored as text, or dates stored as text.