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.

Skip to the 3 exercises ↓

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 as sum_range.
criteria1
The condition for that range: "West", ">=1000", a cell like F2, 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.
Live exampleSales for the region in F2 and the rep in G2
H2fx
ABCDEFGH
1DateRegionRepAmountRegionRepTotal
21/8/2026WestDana1200WestDana4020
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720

How Excel reads it

  1. It walks down all the ranges together, one row at a time.
  2. A row passes when its region meets the first condition and its rep meets the second. Every condition has to hold.
  3. It adds the cells of sum_range on 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 &.

Live exampleFebruary sales: two conditions on one column
H2fx
ABCDEFGH
1DateRegionRepAmountFromToTotal
21/8/2026WestDana12002/1/20262/28/20263505
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720

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!:

Live example#VALUE!: SUMIF order inside SUMIFS
G2fx
ABCDEFG
1DateRegionRepAmountRegionTotal
21/8/2026WestDana1200West#VALUE!
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720

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.

Live example#VALUE!: one range a row short
G2fx
ABCDEFG
1DateRegionRepAmountRegionTotal
21/8/2026WestDana1200West#VALUE!
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720

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.

Exercise 1Two conditions

In F2, add up the West orders of $1,000 or more.

F2fx
ABCDEF
1DateRegionRepAmountWest, 1000+
21/8/2026WestDana1200
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720
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.

Exercise 2Conditions read from cells

F2 holds a rep and G2 an amount. In H2, add up that rep's orders that are larger than the amount in G2.

H2fx
ABCDEFGH
1DateRegionRepAmountRepOverTotal
21/8/2026WestDana1200Dana1000
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720
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.

Exercise 3A region inside a date range

F2 and G2 hold two dates. In H2, add up the West orders dated from F2 to G2, both dates included.

H2fx
ABCDEFGH
1DateRegionRepAmountFromToWest total
21/8/2026WestDana12001/27/20262/19/2026
31/15/2026EastMarcus850
41/27/2026WestPriya640
52/3/2026WestDana2100
62/11/2026EastMarcus430
72/19/2026WestPriya975
83/2/2026EastDana1560
93/10/2026WestDana720
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.

Keep going

Lesson 5 is seven drills on a sales log: COUNTIF, SUMIF, SUMIFS, COUNTIFS and a conditional average, starting from one condition and building up to two, then to conditions read from a cell. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

SUMIF function, with live examplesCOUNTIFS function, with live examplesAVERAGEIF and AVERAGEIFS functions, with live examplesEOMONTH function, with live examples#VALUE! error in Excel: meaning and fixesSUMIFS and COUNTIFS practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: SUMIFS function, Microsoft Support: How to correct a #VALUE! error in the SUMIF/SUMIFS function. Last checked November 2026.