AVERAGEIF function
Quick answer
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)
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.
Answers that use AVERAGEIF
In other languages
AVERAGEIF is PROMEDIO.SI in Spanish Excel and MITTELWERTWENN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .