AVERAGEIFS: the average of the rows that match
AVERAGEIFS averages only the rows that match your conditions: =AVERAGEIFS(C2:C9,B2:B9,"Red") is the average score for the Red team. AVERAGEIF does the same with one condition. It works just like SUMIFS, with the numbers to average first.
A worked example
What's the average score for the Red team?
- Red rows: Ann 55, Cy 70, Fay 48.
- Total 173, from 3 people.
- 173 ÷ 3 = 57.7.
=AVERAGEIFS(C2:C9,B2:B9,F2)
The order is: the numbers to average, then each range to check followed by what to look for. Pointing at F2 rather than typing "Red" lets you copy the formula down for every team.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Score | Team | Average | ||
| 2 | Ann | Red | 55 | Red | 57.667 | ||
| 3 | Bob | Blue | 40 | Blue | 50.333 | ||
| 4 | Cy | Red | 70 | Green | 58.5 | ||
| 5 | Di | Green | 35 | ||||
| 6 | Ed | Blue | 60 | ||||
| 7 | Fay | Red | 48 | ||||
| 8 | Gus | Green | 82 | ||||
| 9 | Hal | Blue | 51 |
Two conditions
Add another range and what to look for. The average for Red, counting only scores of 50 or more: =AVERAGEIFS(C2:C9,B2:B9,"Red",C2:C9,">=50") gives (55 + 70) ÷ 2 = 62.5. For a condition from a cell, join it on: ">="&H1.
Common mistakes
#DIV/0!
No rows match, so there's nothing to average. Check the spelling, or wrap it in IFERROR.
The parts in the wrong order
AVERAGEIF (one condition) puts the range to check first and the numbers last: =AVERAGEIF(B2:B9,"Red",C2:C9). AVERAGEIFS puts the numbers first. Using AVERAGEIFS every time saves remembering both.
Ranges that move when copied
Without $ signs, the ranges slide down and later teams lose rows. Lock them with F4.
Try it now
In G2, work out the average score for the team in G1. Point at G1 rather than typing the team name.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Score | Team | Blue | ||
| 2 | Ann | Red | 55 | Average | |||
| 3 | Bob | Blue | 40 | ||||
| 4 | Cy | Red | 70 | ||||
| 5 | Di | Green | 35 | ||||
| 6 | Ed | Blue | 60 | ||||
| 7 | Fay | Red | 48 | ||||
| 8 | Gus | Green | 82 | ||||
| 9 | Hal | Blue | 51 |