Function guides ยท Statistical
AVERAGEIFS function
Averages the values in a range that meet every one of several conditions.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Argument | What it is |
|---|---|
average_range | The cells to average. |
criteria_range1 | The first range to test. |
criteria1 | The condition for that range. |
... | Up to 127 range and condition pairs. |
What AVERAGEIFS does
AVERAGEIFS averages the values in one range, using only the rows where every listed condition is true. It is the multi-condition cousin of AVERAGEIF.
The argument order mirrors SUMIFS: the numbers to average come first, followed by as many pairs of range and condition as you need. All ranges must be the same size.
It is the function behind summary tables that show a typical value by two categories, such as the average order by region and month. A combination that has no rows returns #DIV/0!, which is worth planning for.
A good habit is to check one cell of the result by hand. Filter the data to the same conditions, select the average column, and read the Average in the status bar at the bottom of the window. If it matches your formula, the ranges and criteria are lined up. If it does not, the usual culprits are a range that starts one row off from the others, or a criterion with a trailing space.
For a typical value that is robust to outliers, there is no conditional MEDIAN function. Use MEDIAN with an IF inside, which is covered on the MEDIAN page.
AVERAGEIFS examples
1. Average by region and product
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sale | ||
| 2 | West | Kit | 450 | 362.5 | |
| 3 | East | Kit | 900 | ||
| 4 | West | Pro | 620 | ||
| 5 | West | Kit | 275 |
Only rows that are West and Kit count, so the average is of 450 and 275, giving 362.5. The first argument is the column to average, followed by pairs of range and condition.
2. Average inside a value band
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 460 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | North | 310 | ||
| 6 | West | 275 |
The same range can be used twice to set a lower and upper bound. This averages the sales from 300 up to 700, which is 450, 620 and 310.
3. A guard against no matches
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sale | ||
| 2 | West | Kit | 450 | No sales | |
| 3 | East | Pro | 900 |
No row is both North and Kit, so AVERAGEIFS has nothing to average and would return #DIV/0!. IFERROR swaps in a readable message.
Common AVERAGEIFS mistakes
- Putting the average range last. AVERAGEIF puts it last. AVERAGEIFS puts it first. Reversing them returns wrong numbers or errors.
- Ranges of different sizes. All the ranges must be the same shape or the function returns #VALUE!.
- Unprotected With no matching rows the result is an error. Handle it with IFERROR when empty groups are expected, as in a monthly table with some quiet months.
- Using OR logic. All conditions must hold. For West or East, average each separately or use a helper column.
- Text numbers in the average range. Text cells are ignored, so the mean uses fewer rows than you think.
AVERAGEIFS on Excel job tests
These appear as a summary table: the average sale by region and product, or by month and rep. The first argument is the column to average, followed by pairs of range and condition.
Take the labels from the table's headers with mixed references, such as AVERAGEIFS($C$2:$C$100,$A$2:$A$100,$E2,$B$2:$B$100,F$1), and fill right and down. A combination that never occurred returns #DIV/0!; if the task does not say what to show, wrap it in IFERROR and return a dash, since a table with scattered errors reads badly.
Related functions
AVERAGEIFS questions
- What is the order of arguments in AVERAGEIFS?
- The range to average first, then pairs of criteria range and criterion.
- Can AVERAGEIFS use dates?
- Yes. Use comparisons such as
">="&DATE(2026,3,1)on a date column. - Why does AVERAGEIFS show
- No row met all the conditions. Check the criteria or wrap in IFERROR.
- How many conditions are allowed?
- Up to 127 pairs.
Practice AVERAGEIFS
The lesson AVERAGEIF and AVERAGEIFS (module 8 of Everyday Excel) has checked AVERAGEIFS exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample