The AVERAGEIFS function in Excel
AVERAGEIFS averages the numbers in one column for the rows where all of your conditions are true, like the average resolution time for high-priority email tickets. It works like SUMIFS, with the range to average first and then pairs of ranges and conditions. Use it whenever a report asks for an average "by" something, or "where" more than one thing is true.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
| average_range | The cells to average. It comes first, unlike in AVERAGEIF. |
|---|---|
| criteria_range1 | The first range to test. |
| criteria1 | The condition for criteria_range1, like "Email", ">=4" or a cell such as F2. |
| criteria_range2, criteria2, ... | Optional. More range and condition pairs. Each range must be the same size as average_range. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In G2, use AVERAGEIFS to find the average Order value for the region in E2 and the channel in F2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order value | Average | ||||||
| 2 | Online | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | ||||||||
| 11 | ||||||||
| 12 |
Tip: Start with the Order value column, then add one range and condition pair for Region and one for Channel.
Examples
=AVERAGEIFS(D2:D9,B2:B9,"Email",C2:C9,"High")→ 4.3Average hours to resolve tickets that came in by Email and were High priority.
=AVERAGEIFS(E2:E9,A2:A9,"Maya",D2:D9,"<=4")→ 4.5Maya's average satisfaction score on tickets she closed in 4 hours or less. A number condition goes in quotes.
=IFERROR(AVERAGEIFS(D2:D9,B2:B9,"Chat",C2:C9,"Low"),"No tickets")→ "No tickets"No chat ticket is Low priority, so AVERAGEIFS would return #DIV/0!. IFERROR shows a message instead.
Example data
| 1 | Agent | Channel | Priority | Hours | CSAT |
|---|---|---|---|---|---|
| 2 | Maya | High | 3.5 | 4 | |
| 3 | Omar | Chat | High | 1.2 | 5 |
| 4 | Maya | Low | 8 | 3 | |
| 5 | Lena | Phone | High | 2.4 | 4 |
| 6 | Omar | High | 5.1 | 3 | |
| 7 | Lena | Low | 6.5 | 4 | |
| 8 | Maya | Chat | High | 0.8 | 5 |
| 9 | Omar | Phone | Low | 4.2 | 4 |
Common mistakes
Average range in the wrong place
AVERAGEIF puts the average range last, but AVERAGEIFS puts it first:
AVERAGEIFS(D2:D9, ...). Mixing them up averages the wrong column or gives #VALUE!.#DIV/0! error
No row meets every condition, so there's nothing to average. Check each condition for typos, or wrap the formula in IFERROR.
Ranges of different sizes
Every criteria range must cover the same rows as
average_range, likeD2:D9withB2:B9. Otherwise Excel returns #VALUE!.Number conditions without quotes
Write comparisons as text:
">=4", not>=4. To compare with a cell, join it:">="&F1.
FAQ
What is the difference between AVERAGEIF and AVERAGEIFS?+
AVERAGEIF takes one condition and puts the average range last. AVERAGEIFS takes one or more conditions and puts the average range first, so it covers both cases.
Can AVERAGEIFS use OR logic?+
Not directly; all conditions must be true. For an OR, add the matching SUMIFS together and divide by the matching COUNTIFS.
Does AVERAGEIFS ignore blank cells?+
Yes. Empty cells in the average range are left out of both the total and the count. Cells that contain 0 are included.
Lesson
Master AVERAGEIFS step by step: AVERAGEIF, MAXIFS and MINIFS
Find the average, highest and lowest values that meet one or more conditions.