Excel like a finance pro.
Statistical function
AVERAGEIF
Averages the cells that meet one condition.
When to use it
The mean of the cells that meet one condition. Same argument pattern as SUMIF: the range to test, the criteria, then optionally the range to average.
The shape of it
- Syntax
=AVERAGEIF(range, criteria, [average_range])
Worked examples
Average for one region
=AVERAGEIF(C2:C7,"West",G2:G7) → 166.6666667
Regions West, East, West, North, West, East with sales 100, 200, 150, 300, 250, 400. West averages 500 over 3.
Average above a threshold
=AVERAGEIF(G2:G7,">200") → 316.6666667
Cells over 200: 300, 250, 400.
Ignore zeros
=AVERAGEIF(G2:G7,"<>0") → 233.3333333
Skips zeros, which plain AVERAGE would count.
Worth knowing
- Reference the condition from a cell so the sheet doubles as a report.
- Wildcards work in text criteria: "*Corp*".
- For several conditions use AVERAGEIFS.
Where it goes wrong
- #DIV/0! when no cell matches.
- The average range is resized to match the criteria range from its top-left cell, so misaligned ranges give wrong answers silently.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.