How to average based on criteria in Excel (AVERAGEIF)
Formula (Excel and Google Sheets)
=AVERAGEIF(B2:B10,F1,C2:C10)Use =AVERAGEIF(B2:B10,F1,C2:C10): it averages the values in C on the rows where B matches F1.
- Result on the example
- 4.25 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
AVERAGEIF looks down B2:B10 for the team in F1 and averages the hours on those rows only: (4.5 + 2 + 7 + 3.5) ÷ 4 = 4.25. The arguments follow SUMIF's order: range to test, condition, range to average.
=AVERAGEIF(B2:B10,F1,C2:C10)Result4.25
B2:B109 rows × 1 col: "Billing", "Tech", "Billing", "Shipping", …A range: 9 cells in one column.
F1"Billing"The value in F1.
C2:C109 rows × 1 col: 4.5, 12, 2, 6.5, …A range: 9 cells in one column.
AVERAGEIF(B2:B10,F1,C2:C10)4.25AVERAGEIF: Average of values that meet a condition.- range
B2:B109 rows × 1 col: "Billing", "Tech", "Billing", "Shipping", … - criteria
F1"Billing" - average_range
C2:C109 rows × 1 col: 4.5, 12, 2, 6.5, …
- range
In Google Sheets
The same formula works in Google Sheets: =AVERAGEIF(B2:B10,F1,C2:C10). We ran it in Google Sheets mode on the same data and it returns 4.25.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =AVERAGEIFS(C2:C10,B2:B10,F1) | 4.25 | AVERAGEIFS puts the average range first and accepts more conditions. |
| =SUMIF(B2:B10,F1,C2:C10)/COUNTIF(B2:B10,F1) | 4.25 | The same average from a sum and a count; useful in very old versions. |
Common mistakes
- If no row matches, AVERAGEIF returns #DIV/0!. Wrap it in IFERROR to show a dash or text instead.
- Without the third argument, AVERAGEIF averages the cells it tests, which only makes sense when the condition is on the numbers themselves.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Average based on criteria in Excel (AVERAGEIF)
F2, calculate the average hours to resolve a ticket for the team in F1.Questions
How do I average values greater than a number?
Use the operator in quotes: =AVERAGEIF(C2:C10,">5").
How do I average for every team at once?
In Excel 365, put =UNIQUE(B2:B10) in one column and =AVERAGEIF(B2:B10,UNIQUE(B2:B10),C2:C10) next to it. In Google Sheets, wrap the second formula in ARRAYFORMULA.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.