SUMIF practice exercises
5 graded problems with realistic data, from easy to medium.
Adds up only the cells that meet one condition: the single-criterion sibling of SUMIFS. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the SUMIF guide first: syntax, a worked example and the mistakes people make.
In this set
- Total sales for one category
- Total only the values above a threshold
- Total hours logged against one project
- Total everything except one category
- Use SUMIF inside a larger formula
The exercises
Total up the sales for just the "East" region.
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | East | 1200 |
| 3 | West | 900 |
| 4 | East | 1500 |
| 5 | West | 700 |
| 6 | East | 1100 |
| 7 | East total | ? |
Enter a formula in B7.
Show a hint
SUMIF(range, criteria, sum_range) checks range for the criteria and totals the matching rows of sum_range: =SUMIF(A2:A6,"East",B2:B6).
SUMIF is SUMIFS with exactly one condition: learn it first and SUMIFS is just "the same thing, more conditions."
Total only the invoices worth more than $500.
| A | B | |
|---|---|---|
| 1 | Invoice | Amount |
| 2 | 1001 | 320 |
| 3 | 1002 | 650 |
| 4 | 1003 | 480 |
| 5 | 1004 | 910 |
| 6 | 1005 | 540 |
| 7 | Total over $500 | ? |
Enter a formula in B7.
Show a hint
When the range you're checking and the range you're summing are the same, you can skip sum_range entirely: =SUMIF(B2:B6,">500").
A criteria like ">500" works in SUMIF exactly the way it does in COUNTIF; the comparison operators you already know carry straight over.
Total the hours logged against the "Redesign" project.
| A | B | |
|---|---|---|
| 1 | Project | Hours |
| 2 | Redesign | 4 |
| 3 | Migration | 6 |
| 4 | Redesign | 3 |
| 5 | Redesign | 5 |
| 6 | Migration | 2 |
| 7 | Redesign total | ? |
Enter a formula in B7.
Show a hint
=SUMIF(A2:A6,"Redesign",B2:B6), text criteria match case-insensitively, same as COUNTIF.
Timesheets, expense reports, anything logged against a category: SUMIF is how that category gets totaled without a pivot table.
Total every expense that is NOT "Rent".
| A | B | |
|---|---|---|
| 1 | Category | Expense |
| 2 | Rent | 1800 |
| 3 | Utilities | 220 |
| 4 | Supplies | 90 |
| 5 | Rent | 1800 |
| 6 | Travel | 340 |
| 7 | Total excluding Rent | ? |
Enter a formula in B7.
Show a hint
The "<>" operator means "not equal to": =SUMIF(A2:A6,"<>Rent",B2:B6).
"<>" flips any SUMIF/COUNTIF/SUMIFS condition into its opposite: just as useful for excluding one thing as ">"/"<" are for a numeric cutoff.
A department's budget is $3,000. Total its actual spending from the table, then find how much budget is left.
| A | B | |
|---|---|---|
| 1 | Department | Spend |
| 2 | Marketing | 800 |
| 3 | Sales | 650 |
| 4 | Marketing | 1100 |
| 5 | Sales | 400 |
| 6 | Marketing | 300 |
| 7 | Budget | 3000 |
| 8 | Marketing budget remaining | ? |
Enter a formula in B8.
Show a hint
Compute the SUMIF first, then subtract it from the budget: =B7-SUMIF(A2:A6,"Marketing",B2:B6).
SUMIF rarely stands alone in a real sheet: it's almost always one piece feeding into a bigger "actual vs. budget" or "actual vs. target" formula like this one.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the SUMIF exercises →