Excel function guide · Conditional sums & counts
AVERAGEIFS function in Excel
AVERAGEIFS returns the average of the numbers in one range for the rows that meet all of your conditions, for example the average online order in the North region. If no rows match, it returns #DIV/0!.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
=AVERAGEIF(range, criteria, [average_range])
| Argument | Required? | What it means |
|---|---|---|
| average_range | Required | The numbers to average. |
| criteria_range1 | Required | The first range to test, the same size as average_range. |
| criteria1 | Required | Its condition: a value, a cell or a comparison in quotes (">100"). |
| criteria_range2, criteria2, … | Optional | More pairs; a row is included only if it meets them all. |
AVERAGEIFS examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Channel | Order value |
| 2 | North | Online | 120 |
| 3 | South | Store | 85 |
| 4 | North | Store | 60 |
| 5 | North | Online | 150 |
| 6 | South | Online | 99 |
| 7 | South | Store | 73 |
1. One condition
=AVERAGEIFS(C2:C7, A2:A7, "North")
Result: 110
The three North orders: (120 + 60 + 150) ÷ 3.
2. Two conditions
=AVERAGEIFS(C2:C7, A2:A7, "South", B2:B7, "Store")
Result: 79
South store orders only: (85 + 73) ÷ 2.
3. Nothing matches
=AVERAGEIFS(C2:C7, A2:A7, "East")
Result: #DIV/0!
No East rows, so there's nothing to divide by. Wrap it in IFERROR to show a message instead.
Try it now
Practise AVERAGEIFS in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In E3, average the order value for the channel in E2.
Common AVERAGEIFS mistakes
#DIV/0! when nothing matches
If no rows meet the conditions, AVERAGEIFS returns #DIV/0!. Use =IFERROR(AVERAGEIFS(…), "No orders") where empty groups are possible.
AVERAGEIF's different order
AVERAGEIF is (range, criteria, average_range), with the numbers last; AVERAGEIFS puts them first. Use AVERAGEIFS for both and you never have to remember.
Zeros versus blanks
Blank cells in average_range are ignored, but zeros are averaged in. A 0 typed for missing data pulls the average down.
Ranges of different sizes
Each criteria range must be the same size as average_range, or the formula returns #VALUE!.
Practise this in Excel Quest
AVERAGEIF(S), MAXIFS & MINIFS Members
Course 5 · Conditional Aggregation · 12 min
Analyse pay by department, level and location — the questions every HR business partner gets asked.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.