# AVERAGEIF function in Excel

> AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero.

Source: https://spreadsheetpractice.com/functions/averageif · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero.
- **Syntax:** `=AVERAGEIF(range, criteria, [average_range])`
- **Most common use:** `=AVERAGEIF(B2:B9,"Chat",C2:C9)` returns 4.67 (Average reply time for chat tickets only)
- **Available in:** Every current version
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The average of the matching values, or #DIV/0! if nothing matches.
- **Google Sheets:** Also available in Google Sheets.

## What it does

AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero. Like SUMIF, it checks one range and averages the matching cells from another, or the checked cells themselves if you leave out the third argument.

## Syntax

`=AVERAGEIF(range, criteria, [average_range])`

| Argument | Required? | What it is |
|---|---|---|
| `range` | Required | The cells to check against the condition. |
| `criteria` | Required | The condition, such as "Chat", ">0" or a cell reference. |
| `average_range` | Optional | The cells to average. If omitted, AVERAGEIF averages the cells in range. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | `=AVERAGEIF(B2:B9,"Chat",C2:C9)` | 4.67 | Average reply time for chat tickets only |
| F3 | `=AVERAGEIF(C2:C9,">0")` | 45.86 | No average range: averages the checked cells, skipping zeros |
| F4 | `=IFERROR(AVERAGEIF(B2:B9,"Fax",C2:C9),"None")` | None | No Fax tickets would give #DIV/0!, so IFERROR shows "None" |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Ticket | Channel | Minutes to reply |  | Check | Result |
| 2 | T-1 | Chat | 4 |  | Chat average | 4.67 |
| 3 | T-2 | Email | 95 |  | Ignoring zeros | 45.86 |
| 4 | T-3 | Chat | 7 |  | Fax, with fallback | None |
| 5 | T-4 | Phone | 0 |  |  |  |
| 6 | T-5 | Email | 140 |  |  |  |
| 7 | T-6 | Chat | 3 |  |  |  |
| 8 | T-7 | Phone | 12 |  |  |  |
| 9 | T-8 | Email | 60 |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #DIV/0! | Nothing matched the condition. Check the spelling of the criterion, or wrap the formula in IFERROR. |
| The average range is offset | average_range should start on the same row as range. If it starts lower, the matches line up with the wrong values. |

## Tips

- Use =AVERAGEIF(range,"<>0") to average and ignore zeros that mean "no data".

## Related functions

- [AVERAGEIFS](https://spreadsheetpractice.com/functions/averageifs): AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only.
- [AVERAGE](https://spreadsheetpractice.com/functions/average): AVERAGE adds up the numbers you give it and divides by how many numbers there are.
- [SUMIF](https://spreadsheetpractice.com/functions/sumif): SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500.
- [COUNTIF](https://spreadsheetpractice.com/functions/countif): COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp".

## Questions

### How do I average only cells that meet a condition?

Use =AVERAGEIF(B2:B9,"Chat",C2:C9), or AVERAGEIFS for more than one condition.

### How do I average and ignore zeros?

Use =AVERAGEIF(C2:C9,"<>0") or ">0" if there are no negative values.

## Practice

A graded AVERAGEIF exercise ("Average order by region") is on [the AVERAGEIF page](https://spreadsheetpractice.com/functions/averageif). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
