Statistics formula
AVERAGEIFS in Excel and Google Sheets
Average numbers that meet one or more conditions.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)- average_range
- The numbers to average.
- criteria_range1
- The column to test.
- criteria1
- The condition.
Example
| Row | A | B |
|---|---|---|
| 1 | Rep | Deal |
| 2 | Anna | 4,000 |
| 3 | Ben | 2,500 |
| 4 | Anna | 6,000 |
| 5 | ||
| 6 | Anna average |
B6 =AVERAGEIFS(B2:B4,A2:A4,"Anna")
Result: 5000
Averages deals where the rep is Anna.
Ready-to-copy formulas
Average order value per month
=AVERAGEIFS(C:C,B:B,">="&E1,B:B,"<"&E2)
Tips
- Returns #DIV/0! when nothing matches. Wrap it in IFERROR.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.