Excel SUMIFS function: add with two or more conditions
SUMIFS adds values that meet every condition you give it. The range to add goes first. =SUMIFS(B2:B7,E2:E7,"East",B2:B7,">1000") adds sales that are in the East and over 1,000.
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Each condition is a pair: where to look, then what to look for. A row is added only when it passes every pair.
Examples
| Formula | Result |
|---|---|
=SUMIFS(B2:B7,E2:E7,"East",B2:B7,">1000") | 4,950 |
=SUMIFS(B2:B7,E2:E7,"West",B2:B7,">900") | 990 |
SUMIF vs SUMIFS argument order
| Function | Range to add |
|---|---|
| SUMIF | Last: =SUMIF(E2:E7,"East",B2:B7) |
| SUMIFS | First: =SUMIFS(B2:B7,E2:E7,"East") |
Many people just use SUMIFS for everything, even one condition, so they only have to remember one order.
Common mistakes
- Putting the range to add last, out of SUMIF habit.
- Ranges of different sizes, like B2:B7 with E2:E8. Excel returns #VALUE!.
- Expecting OR logic. SUMIFS is always AND. For "East or West," add two SUMIFS together.
Counting and averaging the same way
COUNTIFS and AVERAGEIFS take the same condition pairs. COUNTIFS has no range to add. AVERAGEIFS puts the range to average first, like SUMIFS.
Try it: Big East sales total
Add up the sales that are in the East region and over 1000. Put the total in B8.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Can SUMIFS use OR logic?
Not directly. Add two SUMIFS together, one per option, like East plus West.
Can I use dates as conditions?
Yes. Use a comparison joined to a date, like ">="&DATE(2026,1,1).
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use SUMIF in Excel, the argument order that trips people up, using cell references in the condition, and a live exercise.
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
A one-page cheat sheet of the Excel formulas that show up on pre-employment tests, with the syntax, a real example and the mistake to avoid for each.