# How to average based on criteria in Excel (AVERAGEIF)

Use `=AVERAGEIF(B2:B10,F1,C2:C10)`: it averages the values in C on the rows where B matches F1.

**Excel:** `=AVERAGEIF(B2:B10,F1,C2:C10)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Ticket | Team | Hours to resolve |  | Team | Billing |
| T-301 | Billing | 4.5 |  | Average hours |  |
| T-302 | Tech | 12 |  |  |  |
| T-303 | Billing | 2 |  |  |  |
| T-304 | Shipping | 6.5 |  |  |  |
| T-305 | Tech | 9.5 |  |  |  |
| T-306 | Billing | 7 |  |  |  |
| T-307 | Shipping | 3 |  |  |  |
| T-308 | Billing | 3.5 |  |  |  |
| T-309 | Tech | 15 |  |  |  |

Formula in F2: `=AVERAGEIF(B2:B10,F1,C2:C10)`

Result: 4.25

## 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.

## Other ways

- `=AVERAGEIFS(C2:C10,B2:B10,F1)`: AVERAGEIFS puts the average range first and accepts more conditions.
- `=SUMIF(B2:B10,F1,C2:C10)/COUNTIF(B2:B10,F1)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/averageif-average-based-on-criteria