Excel practice › Excel functions › SUMIFS
SUMIFS in Excel: add up the rows that meet several conditions
SUMIFS adds up the numbers on the rows that meet every condition you give it. With regions in B2:B9, reps in C2:C9 and amounts in D2:D9, Dana's sales in the West are =SUMIFS(D2:D9,B2:B9,"West",C2:C9,"Dana").
The range to add comes first, followed by pairs: a range, then the condition for it. That is the reverse of SUMIF, where the range to add comes last.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range- The cells to add. Always the first argument.
criteria_range1- The range tested with
criteria1. It has to have the same number of rows and columns assum_range. criteria1- The condition for that range:
"West",">=1000", a cell likeF2, or an operator joined to a cell,">="&F2. criteria_range2, criteria2- More pairs, optional, up to 127 pairs in all. A row is added only when it meets every one of them.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | Region | Rep | Total | |
| 2 | 1/8/2026 | West | Dana | 1200 | West | Dana | 4020 | |
| 3 | 1/15/2026 | East | Marcus | 850 | ||||
| 4 | 1/27/2026 | West | Priya | 640 | ||||
| 5 | 2/3/2026 | West | Dana | 2100 | ||||
| 6 | 2/11/2026 | East | Marcus | 430 | ||||
| 7 | 2/19/2026 | West | Priya | 975 | ||||
| 8 | 3/2/2026 | East | Dana | 1560 | ||||
| 9 | 3/10/2026 | West | Dana | 720 |
F2 with "East" to get that one: 1560.How Excel reads it
- It walks down all the ranges together, one row at a time.
- A row passes when its region meets the first condition and its rep meets the second. Every condition has to hold.
- It adds the cells of
sum_rangeon the rows that passed.
There is no "or" inside SUMIFS. For one rep or another, add two SUMIFS: =SUMIFS(D2:D9,C2:C9,"Priya")+SUMIFS(D2:D9,C2:C9,"Marcus") gives 2895 on this list.
Between two dates
A date range is two conditions on the same column: on or after the first date, and on or before the last one. Each is an operator in quotes joined to a cell with &.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | From | To | Total | |
| 2 | 1/8/2026 | West | Dana | 1200 | 2/1/2026 | 2/28/2026 | 3505 | |
| 3 | 1/15/2026 | East | Marcus | 850 | ||||
| 4 | 1/27/2026 | West | Priya | 640 | ||||
| 5 | 2/3/2026 | West | Dana | 2100 | ||||
| 6 | 2/11/2026 | East | Marcus | 430 | ||||
| 7 | 2/19/2026 | West | Priya | 975 | ||||
| 8 | 3/2/2026 | East | Dana | 1560 | ||||
| 9 | 3/10/2026 | West | Dana | 720 |
">="&DATE(2026,2,1).With only the first day of the month in F2, EOMONTH supplies the last day: =SUMIFS(D2:D9,A2:A9,">="&F2,A2:A9,"<="&EOMONTH(F2,0)) gives the same 3505.
The argument order, and two more ways it breaks
1. Arguments in SUMIF order. SUMIF is written range, condition, range to add. Typed that way into SUMIFS, the Region column becomes the range to add and the single cell F2 becomes a criteria range. The two don't have the same size, and the result is #VALUE!:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | Region | Total | |
| 2 | 1/8/2026 | West | Dana | 1200 | West | #VALUE! | |
| 3 | 1/15/2026 | East | Marcus | 850 | |||
| 4 | 1/27/2026 | West | Priya | 640 | |||
| 5 | 2/3/2026 | West | Dana | 2100 | |||
| 6 | 2/11/2026 | East | Marcus | 430 | |||
| 7 | 2/19/2026 | West | Priya | 975 | |||
| 8 | 3/2/2026 | East | Dana | 1560 | |||
| 9 | 3/10/2026 | West | Dana | 720 |
D2:D9 to the front: =SUMIFS(D2:D9,B2:B9,F2) gives 5635.2. Ranges of different sizes. Every criteria range has to cover the same rows as the sum range. One row short is enough for #VALUE!. It happens when rows are added to the data and only some of the ranges in the formula get extended.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | Region | Total | |
| 2 | 1/8/2026 | West | Dana | 1200 | West | #VALUE! | |
| 3 | 1/15/2026 | East | Marcus | 850 | |||
| 4 | 1/27/2026 | West | Priya | 640 | |||
| 5 | 2/3/2026 | West | Dana | 2100 | |||
| 6 | 2/11/2026 | East | Marcus | 430 | |||
| 7 | 2/19/2026 | West | Priya | 975 | |||
| 8 | 3/2/2026 | East | Dana | 1560 | |||
| 9 | 3/10/2026 | West | Dana | 720 |
B2:B9 and the total (5635) comes back.3. A cell reference inside the quotes. ">=F2" is the text >=F2, not the value of the cell. Nothing matches and the sum is 0, with no error. Write ">="&F2. Text conditions need their quotes too: "West", not West.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.
In F2, add up the West orders of $1,000 or more.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | West, 1000+ | |
| 2 | 1/8/2026 | West | Dana | 1200 | ||
| 3 | 1/15/2026 | East | Marcus | 850 | ||
| 4 | 1/27/2026 | West | Priya | 640 | ||
| 5 | 2/3/2026 | West | Dana | 2100 | ||
| 6 | 2/11/2026 | East | Marcus | 430 | ||
| 7 | 2/19/2026 | West | Priya | 975 | ||
| 8 | 3/2/2026 | East | Dana | 1560 | ||
| 9 | 3/10/2026 | West | Dana | 720 |
Show the answer
=SUMIFS(D2:D9,B2:B9,"West",D2:D9,">=1000") gives 3300.
The amounts go first because they are what gets added. Then two pairs: region equals West, amount at least 1000. Column D is both the sum range and a criteria range, which is fine: 1200 + 2100.
F2 holds a rep and G2 an amount. In H2, add up that rep's orders that are larger than the amount in G2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | Rep | Over | Total | |
| 2 | 1/8/2026 | West | Dana | 1200 | Dana | 1000 | ||
| 3 | 1/15/2026 | East | Marcus | 850 | ||||
| 4 | 1/27/2026 | West | Priya | 640 | ||||
| 5 | 2/3/2026 | West | Dana | 2100 | ||||
| 6 | 2/11/2026 | East | Marcus | 430 | ||||
| 7 | 2/19/2026 | West | Priya | 975 | ||||
| 8 | 3/2/2026 | East | Dana | 1560 | ||||
| 9 | 3/10/2026 | West | Dana | 720 |
Show the answer
=SUMIFS(D2:D9,C2:C9,F2,D2:D9,">"&G2) gives 4860.
A text condition can be the cell itself (F2). A comparison needs the operator in quotes, joined to the cell: ">"&G2. Dana has three orders over 1000: 1200 + 2100 + 1560.
F2 and G2 hold two dates. In H2, add up the West orders dated from F2 to G2, both dates included.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Rep | Amount | From | To | West total | |
| 2 | 1/8/2026 | West | Dana | 1200 | 1/27/2026 | 2/19/2026 | ||
| 3 | 1/15/2026 | East | Marcus | 850 | ||||
| 4 | 1/27/2026 | West | Priya | 640 | ||||
| 5 | 2/3/2026 | West | Dana | 2100 | ||||
| 6 | 2/11/2026 | East | Marcus | 430 | ||||
| 7 | 2/19/2026 | West | Priya | 975 | ||||
| 8 | 3/2/2026 | East | Dana | 1560 | ||||
| 9 | 3/10/2026 | West | Dana | 720 |
Show the answer
=SUMIFS(D2:D9,B2:B9,"West",A2:A9,">="&F2,A2:A9,"<="&G2) gives 3715.
Three pairs: region is West, date on or after F2, date on or before G2. The East order inside the dates is skipped, and so are the West orders outside them: 640 + 2100 + 975.
Questions people ask
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition, SUMIFS one or more. The order is the trap: SUMIF is (range, criteria, sum_range) and SUMIFS is (sum_range, criteria_range1, criteria1, ...). SUMIFS with one pair does the same job as SUMIF: =SUMIFS(D2:D9,B2:B9,"West") and =SUMIF(B2:B9,"West",D2:D9) both give 5635 on the list above.
Why does SUMIFS return #VALUE!?
Usually because a criteria range doesn't have the same number of rows and columns as the sum range, or because the arguments were typed in SUMIF order. Microsoft lists two more causes: the formula points at a workbook that is closed, or a criteria text is longer than 255 characters. More causes of this error are on the #VALUE! page.
How do I use SUMIFS between two dates?
Use the date column twice, with one condition for each end: =SUMIFS(D2:D9,A2:A9,">="&F2,A2:A9,"<="&G2), where F2 and G2 hold the first and last date. The second live example on this page does exactly that.
Are there SUMIFS practice exercises with answers?
Three, on this page: two conditions, conditions read from cells, and a region inside a date range. They run in the browser, they're free, and each has its answer and an explanation under "Show the answer". Lesson 5 has seven more drills and is part of Pro.