Confident Cells

AVERAGEIFS: the average of the rows that match

20 November 2026 · 2 min read

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?

  1. Red rows: Ann 55, Cy 70, Fay 48.
  2. Total 173, from 3 people.
  3. 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.

ABCDEFG
1NameTeamScoreTeamAverage
2AnnRed55Red57.667
3BobBlue40Blue50.333
4CyRed70Green58.5
5DiGreen35
6EdBlue60
7FayRed48
8GusGreen82
9HalBlue51
G2 holds =AVERAGEIFS($C$2:$C$9,$B$2:$B$9,F2), copied down. The $ signs keep the ranges in place.

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

Your turn

In G2, work out the average score for the team in G1. Point at G1 rather than typing the team name.

ABCDEFG
1NameTeamScoreTeamBlue
2AnnRed55Average
3BobBlue40
4CyRed70
5DiGreen35
6EdBlue60
7FayRed48
8GusGreen82
9HalBlue51