Excel practice · Conditional Counting & Averages

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

  1. Total sales for one category
  2. Total only the values above a threshold
  3. Total hours logged against one project
  4. Total everything except one category
  5. Use SUMIF inside a larger formula

The exercises

1. Total sales for one category Easy

Total up the sales for just the "East" region.

AB
1RegionSales
2East1200
3West900
4East1500
5West700
6East1100
7East 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."

2. Total only the values above a threshold Medium

Total only the invoices worth more than $500.

AB
1InvoiceAmount
21001320
31002650
41003480
51004910
61005540
7Total 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.

3. Total hours logged against one project Medium

Total the hours logged against the "Redesign" project.

AB
1ProjectHours
2Redesign4
3Migration6
4Redesign3
5Redesign5
6Migration2
7Redesign 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.

4. Total everything except one category Medium

Total every expense that is NOT "Rent".

AB
1CategoryExpense
2Rent1800
3Utilities220
4Supplies90
5Rent1800
6Travel340
7Total 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.

5. Use SUMIF inside a larger formula Medium

A department's budget is $3,000. Total its actual spending from the table, then find how much budget is left.

AB
1DepartmentSpend
2Marketing800
3Sales650
4Marketing1100
5Sales400
6Marketing300
7Budget3000
8Marketing 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 →

More conditional counting & averages practice

All Excel practice exercises · Excel test prep