AVERAGEIF function
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.
- Available in
- Every current version
- Returns
- The average of the matching values, or #DIV/0! if nothing matches.
- Google Sheets
- Also available in Google Sheets.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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" |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Ticket | Channel | Minutes to reply | Check | Result | |
| T-1 | Chat | 4 | Chat average | 4.67 | |
| T-2 | 95 | Ignoring zeros | 45.86 | ||
| T-3 | Chat | 7 | Fax, with fallback | None | |
| T-4 | Phone | 0 | |||
| T-5 | 140 | ||||
| T-6 | Chat | 3 | |||
| T-7 | Phone | 12 | |||
| T-8 | 60 |
Practice AVERAGEIF
Graded instantly, then re-checked on changed data.
Average order by region
F2, return the average order value for the region named in E2. Fill down to F4.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".
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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.