Excel practice · Conditional Counting & Averages

AVERAGEIFS practice exercises

5 graded problems with realistic data, all medium.

Averages 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 AVERAGEIFS guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Average East region Q1 sales
  2. Average hours on high-priority Design work
  3. Average Downtown food rating
  4. Average large South region orders
  5. Average Cohort B final scores

The exercises

1. Average East region Q1 sales Medium

Find the average sale amount for the East region in Q1 only.

ABCDE
1RegionQuarterSalesRegion:East
2EastQ1100Quarter:Q1
3EastQ1140Average:?
4WestQ190
5EastQ2200
6EastQ1120

Enter a formula in E3.

Show a hint

AVERAGEIFS(avg_range, criteria_range1, criteria1, ...). Same shape as SUMIFS, but averages: =AVERAGEIFS(C2:C6,A2:A6,E1,B2:B6,E2).

A filtered average (not "average of everything," but "average of exactly the rows that matter") is what AVERAGE alone can't do.

2. Average hours on high-priority Design work Medium

Find the average hours logged by the Design team on High priority tasks, across six logged tasks.

ABCDE
1TeamPriorityHoursTeam:Design
2DesignHigh12Priority:High
3DesignLow4Average Hours:?
4EngHigh20
5DesignHigh18
6DesignLow6
7EngLow5

Enter a formula in E3.

Show a hint

One more logged task this time: =AVERAGEIFS(C2:C7,A2:A7,E1,B2:B7,E2).

Team-and-priority averages are how capacity planning actually gets done.

3. Average Downtown food rating Medium

Find the average customer rating for Food category items at the Downtown store (row 1 just names the chain).

ABCDE
1ChainMetro BitesStore:Downtown
2StoreCategoryRatingCategory:Food
3DowntownFood4Average Rating:?
4DowntownRetail3
5UptownFood5
6DowntownFood5
7DowntownFood3

Enter a formula in E3.

Show a hint

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

Store-and-category is a natural two-condition filter for any multi-location business.

4. Average large South region orders Medium

Find the average value of South region orders that are worth $100 or more.

ABCDE
1RegionValueAverage:?
2South140
3South60
4North200
5South180
6South90

Enter a formula in E1.

Show a hint

=AVERAGEIFS(B2:B6,A2:A6,"South",B2:B6,">=100").

Notice the same range (B2:B6) can be both the thing you're averaging AND one of the filter conditions. That's normal.

5. Average Cohort B final scores Medium

Find the average score for Cohort B, counting only their Final attempt (not practice runs).

ABCDEFGH
1RowCohortAttemptScoreCohort:B
21BFinal80Attempt:Final
32AFinal70Average:?
43BPractice60
54BFinal92
65BFinal74

Enter a formula in H3.

Show a hint

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

Excluding practice attempts from a "real" average is exactly the kind of filter AVERAGEIFS is built for.

Solve these in a real grid

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

Start the AVERAGEIFS exercises →

More conditional counting & averages practice

All Excel practice exercises · Excel test prep