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
- Average East region Q1 sales
- Average hours on high-priority Design work
- Average Downtown food rating
- Average large South region orders
- Average Cohort B final scores
The exercises
Find the average sale amount for the East region in Q1 only.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Quarter | Sales | Region: | East |
| 2 | East | Q1 | 100 | Quarter: | Q1 |
| 3 | East | Q1 | 140 | Average: | ? |
| 4 | West | Q1 | 90 | ||
| 5 | East | Q2 | 200 | ||
| 6 | East | Q1 | 120 |
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.
Find the average hours logged by the Design team on High priority tasks, across six logged tasks.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Team | Priority | Hours | Team: | Design |
| 2 | Design | High | 12 | Priority: | High |
| 3 | Design | Low | 4 | Average Hours: | ? |
| 4 | Eng | High | 20 | ||
| 5 | Design | High | 18 | ||
| 6 | Design | Low | 6 | ||
| 7 | Eng | Low | 5 |
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.
Find the average customer rating for Food category items at the Downtown store (row 1 just names the chain).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Chain | Metro Bites | Store: | Downtown | |
| 2 | Store | Category | Rating | Category: | Food |
| 3 | Downtown | Food | 4 | Average Rating: | ? |
| 4 | Downtown | Retail | 3 | ||
| 5 | Uptown | Food | 5 | ||
| 6 | Downtown | Food | 5 | ||
| 7 | Downtown | Food | 3 |
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.
Find the average value of South region orders that are worth $100 or more.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Value | Average: | ? | |
| 2 | South | 140 | |||
| 3 | South | 60 | |||
| 4 | North | 200 | |||
| 5 | South | 180 | |||
| 6 | South | 90 |
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.
Find the average score for Cohort B, counting only their Final attempt (not practice runs).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Row | Cohort | Attempt | Score | Cohort: | B | ||
| 2 | 1 | B | Final | 80 | Attempt: | Final | ||
| 3 | 2 | A | Final | 70 | Average: | ? | ||
| 4 | 3 | B | Practice | 60 | ||||
| 5 | 4 | B | Final | 92 | ||||
| 6 | 5 | B | Final | 74 |
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 →