AVERAGEIFS
Averages a range, but only for rows where every listed condition holds.
AVERAGEIFS is what you reach for when SUMIFS and COUNTIFS alone would make you do the division yourself: average deal size for one desk, average rating for one store category, average handling time for one ticket priority. It follows the same shape as SUMIFS, with the range to average listed first, then condition pairs after it.
One behavior worth knowing up front: AVERAGEIFS applies the same rule as plain AVERAGE to whichever rows match. A matching cell containing 0 counts toward the average; a genuinely blank matching cell doesn't. That distinction rarely matters until the one time it quietly skews a result.
Syntax
| Argument | Description |
|---|---|
| average_range | Required. The range of values to average. |
| criteria_range1, criteria1 | Required. A range to test and the condition it must meet. |
| criteria_range2, criteria2, ... | Optional. More condition pairs, all required. |
The mistake almost everyone makes
Not handling the case where nothing matches. If every condition pair comes up empty, AVERAGEIFS has nothing to average and returns a #DIV/0! error, the same way plain AVERAGE does on an empty range. In a report that runs daily or weekly, a filter combination with zero matching rows is a question of when, not if, so it's worth wrapping the formula in IFERROR whenever the filter might plausibly come up empty: =IFERROR(AVERAGEIFS(...), "No data").
The second mistake, shared with SUMIFS and COUNTIFS, is mixing up which range is being averaged versus which is being tested, or giving a criteria_range a different size than average_range.
A real example: average ticket size by desk and priority
Say you're reviewing trade tickets and need the average size for the Rates desk, filtered to high-priority tickets only:
If Desk_Range and Priority_Range don't cover the same rows as Size_Range, the average silently comes out wrong rather than erroring, which is exactly the SUMIFS-family range-mismatch trap again, just with a division step hiding the symptom even better.
Worked examples
| A | B | C | |
|---|---|---|---|
| 1 | Desk | Priority | Size |
| 2 | Rates | High | 12 |
| 3 | Rates | Low | 4 |
| 4 | Rates | High | 18 |
Common mistakes
- No IFERROR wrapper, so a filter combination with zero matches throws a raw #DIV/0! error straight into a report.
- Mixing up which range is being averaged versus which is being tested.
- Forgetting AVERAGEIFS conditions are AND, same as SUMIFS and COUNTIFS, not OR.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice AVERAGEIFS →