Excel practice · Conditional Counting & Averages

SUMIFS practice exercises

5 graded problems with realistic data, all medium.

Sums a range, but only for rows where every listed condition holds. 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 SUMIFS guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Total West region sales in Q1
  2. Total Sales team travel expenses
  3. Total large North region orders
  4. Total delivered weight for one carrier
  5. Total Growth team Q2 budget

The exercises

1. Total West region sales in Q1 Medium

A sales export lists region, quarter, and revenue. Total the sales for the West region in Q1 only.

ABCDE
1RegionQuarterSalesRegion:West
2WestQ1120Quarter:Q1
3EastQ190Total:?
4WestQ2150
5WestQ180
6EastQ260

Enter a formula in E3.

Show a hint

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). The sum range comes first, then each condition as a pair: =SUMIFS(C2:C6,A2:A6,E1,B2:B6,E2).

SUMIFS is the workhorse for "total, but only where several things are all true." It's the single most common real-world upgrade from SUM.

2. Total Sales team travel expenses Medium

An expense ledger tracks department, category, and amount across six lines. Total everything the Sales department spent on Travel.

ABCDE
1DepartmentCategoryAmountDept:Sales
2SalesTravel400Category:Travel
3MarketingTravel250Total:?
4SalesSupplies120
5SalesTravel180
6SupportTravel90
7MarketingSupplies75

Enter a formula in E3.

Show a hint

One more ledger line this time: =SUMIFS(C2:C7,A2:A7,E1,B2:B7,E2).

This is a real expense-report question. Total by department AND category is exactly what finance reviews every month.

3. Total large North region orders Medium

Total the value of orders from the North region that are worth $100 or more.

ABCDE
1RegionOrder ValueTotal (North, ≥100):?
2North140
3South200
4North60
5North110
6South90

Enter a formula in E1.

Show a hint

A condition can be a comparison string too: =SUMIFS(B2:B6,A2:A6,"North",B2:B6,">=100").

Mixing an exact-match condition with a threshold condition in one SUMIFS is extremely common, like region plus a minimum order size here.

4. Total delivered weight for one carrier Medium

A logistics sheet (row 1 just names the report) tracks carrier, status, and weight. Total the weight FastFreight has actually delivered (not pending).

ABCDE
1ReportLogistics Q3Carrier:FastFreight
2CarrierStatusWeightStatus:Delivered
3FastFreightDelivered12Total Weight:?
4FastFreightPending9
5SwiftCoDelivered15
6FastFreightDelivered7
7SwiftCoPending5

Enter a formula in E3.

Show a hint

=SUMIFS(C3:C7,A3:A7,E1,B3:B7,E2).

Filtering out "Pending" before totaling is the difference between a real number and a misleading one. SUMIFS does the filtering and the totaling in one step.

5. Total Growth team Q2 budget Medium

Total the budget lines belonging to the Growth team for Q2 specifically.

ABCDEFGH
1RowTeamQuarterBudgetTeam:Growth
21GrowthQ15000Quarter:Q2
32GrowthQ26200Total Budget:?
43OpsQ24100
54GrowthQ22300
65OpsQ13900

Enter a formula in H3.

Show a hint

Same idea, data shifted a column over: =SUMIFS(D2:D6,B2:B6,G1,C2:C6,G2).

Budget rollups by team and period are exactly the kind of report SUMIFS replaces a pivot table for, when the sheet is small enough.

Solve these in a real grid

Type your formula, get graded instantly. Your first 10 exercises are free, no signup.

Start the SUMIFS exercises →

More conditional counting & averages practice

All Excel practice exercises · Excel test prep