AVERAGEIFS
Math & AggregationintermediateAVERAGEIFS is the multi-condition version of AVERAGEIF. You point it at the cells you want averaged, then add each criteria range and criteria pair. It's useful when you need a benchmark for a very specific slice of your data such as one team in one region or one product line in one quarter.
Syntax
Excel:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Google Sheets: Same syntax
Parameters
average_rangeThe cells to average.criteria_range1The first range to evaluate.criteria1The condition for the first range.criteria_range2, criteria2?Additional range and condition pairs.? = optional parameter
Worked Example
What is the average salary for remote Customer Success employees in an HR audit file?
| Row numbers | F | ||
|---|---|---|---|
| Header row | department | location | salary |
| 2 | Customer Success | Remote | $68,400 |
| 3 | Customer Success | Remote | $70,250 |
| 4 | Customer Success | Boston | $69,500 |
| 5 | Customer Success | Remote | $71,900 |
| 6 | Customer Success | Remote | $73,150 |
| 7 | People | Remote | $76,500 |
Formula
=AVERAGEIFS(C2:C7, A2:A7, "Customer Success", B2:B7, "Remote")Result
$70,925.00AVERAGEIFS checks rows 2 through 7 and averages only the salaries where department is 'Customer Success' and location is 'Remote'. In this sample, rows 2, 3, 5, and 6 match, producing $70,925.00.
No exercises yet for this formula.