Excel practice › Excel functions › SUMIF

SUMIF in Excel: add up the rows that meet one condition

SUMIF adds up the numbers in a range that meet one condition. With categories in A2:A8 and amounts in C2:C8, the total spent on travel is =SUMIF(A2:A8,"Travel",C2:C8).

The condition can be a text, a number, a comparison like ">200" or a cell. For two or more conditions at once, use SUMIFS.

Skip to the 3 exercises ↓

Syntax

=SUMIF(range, criteria, [sum_range])

range
The cells to test against the condition, for example the Category column.
criteria
The condition: a number (60), a text in quotes ("Travel"), a comparison in quotes (">200") or a cell (E2). Text, and anything with a comparison symbol, has to be in double quotes.
sum_range
The cells to add. Optional: leave it out and SUMIF adds the cells of range itself. It should be the same size and shape as range.
Live exampleTotal for the category in E2
F2fx
ABCDEF
1CategoryItemAmountCategoryTotal
2TravelFlight to Denver412Travel846
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289

How Excel reads it

  1. It goes down A2:A8 and compares each cell with the condition, here the text in E2.
  2. For every cell that matches, it takes the cell in the same position of C2:C8.
  3. It adds those amounts. Rows that don't match are skipped.

The pairing is by position: the first cell of range goes with the first cell of sum_range, the second with the second. That's why the two ranges should start and end on the same rows.

Conditions on numbers

A comparison goes inside double quotes, operator and number together: ">200", "<=50". When the numbers being tested are also the numbers to add, the third argument can be left out.

Live exampleExpenses over $200
E2fx
ABCDE
1CategoryItemAmountOver 200
2TravelFlight to Denver412915
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289

Wildcards: part of a text

In a text condition, * stands for any run of characters and ? for exactly one. On this list, =SUMIF(B2:B8,"Flight*",C2:C8) adds the two flights (701), and "*lunch*" matches any item with the word lunch in it. To match a real asterisk or question mark, put a tilde in front of it: "~*".

The two mistakes that give a wrong total

Neither one shows an error, which is what makes them expensive. Both are live below. Fix the formula in the box and watch the cell.

1. The cell reference is inside the quotes. To compare against a cell, the operator goes in quotes and the cell is joined to it with &: ">"&E2. Written as ">E2", the condition is the text >E2. Cell E2 is never read, and no amount matches:

Live example0: the cell is inside the quotes
F2fx
ABCDEF
1CategoryItemAmountOverTotal
2TravelFlight to Denver4122000
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289

2. The two ranges don't line up. Here the amounts start one row above the categories. Excel still pairs first cell with first cell, so every match picks up the amount from the wrong row:

Live exampleWrong total: ranges one row apart
F2fx
ABCDEF
1CategoryItemAmountCategoryTotal
2TravelFlight to Denver412Travel300
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289

A related case from Microsoft's page: when sum_range is a different size from range, Excel doesn't stop with an error. It adds a block that starts at the first cell of sum_range and has the dimensions of range. Write both ranges with the same first and last row and you never have to think about it.

And the quoting rule, also from Microsoft: a text condition, or any condition with a comparison symbol, goes in double quotes ("Travel", ">200"). A plain number doesn't need them.

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 1Total for one category

In E2, add up everything spent on Meals.

E2fx
ABCDE
1CategoryItemAmountMeals total
2TravelFlight to Denver412
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289
Show the answer

=SUMIF(A2:A8,"Meals",C2:C8) gives 300.

SUMIF checks each cell of A2:A8 against "Meals" and adds the amount on the same row of C2:C8: 86 + 214.

Exercise 2A limit kept in a cell

E2 holds a limit. In F2, add up the expenses of that amount or more, so the total follows E2 when the limit changes.

F2fx
ABCDEF
1CategoryItemAmountLimitTotal
2TravelFlight to Denver412150
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289
Show the answer

=SUMIF(C2:C8,">="&E2) gives 1065.

The operator is text, so it sits in quotes, and & glues the value of E2 to it: SUMIF receives >=150. With no third argument it adds the cells it tests: 412 + 150 + 214 + 289.

Exercise 3A wildcard built from a cell

E2 holds a word. In F2, add up every expense whose item starts with that word.

F2fx
ABCDEF
1CategoryItemAmountStarts withTotal
2TravelFlight to Denver412Flight
3SoftwareDesign suite60
4MealsTeam lunch86
5TravelRental car145
6SoftwareVideo call plan150
7MealsClient dinner214
8TravelFlight to Austin289
Show the answer

=SUMIF(B2:B8,E2&"*",C2:C8) gives 701.

E2&"*" builds the condition Flight*: the word, then anything. Both flights match: 412 + 289.

Questions people ask

Why is my SUMIF returning 0?

Because the condition never matches. Check three things: a cell reference typed inside the quotes (">E2" instead of ">"&E2), ranges that don't start on the same row, and a text that isn't exactly the same in the data. Microsoft's COUNTIF page warns about spaces at the start or end of a cell, and the COUNTIF page here shows one live. The first two are live on this page.

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition. SUMIFS takes one or more, and a row has to meet all of them. The order of the arguments is different: in SUMIF the range to add comes last and is optional, in SUMIFS it comes first. With a single condition both give the same total: =SUMIFS(C2:C8,A2:A8,"Travel") is 846 on the list above, like the SUMIF.

Can SUMIF add up two categories at once?

Not with one condition. Add two SUMIFs: =SUMIF(A2:A8,"Travel",C2:C8)+SUMIF(A2:A8,"Meals",C2:C8) gives 1146 on the list above.

Where can I do SUMIF practice online?

On this page: the three exercises above run in the browser, are free and need no sign-up, and each one has its answer under "Show the answer". The formula playground is a blank sheet for trying your own data, and the free Excel test is 10 questions in a real spreadsheet.

Keep going

Lesson 5 is seven drills on a sales log: COUNTIF, SUMIF, SUMIFS, COUNTIFS and a conditional average, with conditions typed into the formula and 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

SUMIFS function, with live examplesCOUNTIF function, with live examplesAVERAGEIF and AVERAGEIFS functions, with live examplesIF 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: SUMIF function, Microsoft Support: Use the COUNTIF function in Microsoft Excel. Last checked November 2026.