The AVERAGEIFS function in Excel
AVERAGEIFS averages the numbers in rows where all conditions are true: paid orders from the North, scores of one class above a pass mark.
The range to average comes first, followed by range/condition pairs — the same order as SUMIFS. It also works with a single condition.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | What it means |
|---|---|
| average_range | The cells to average. |
| criteria_range1 | The first range to check. |
| criteria1 | The condition for that range: "North", ">=100", a cell, or a wildcard. |
| criteria_range2, criteria2optional | More range/condition pairs. All ranges must be the same size as average_range. |
Examples
1. Paid orders in the North
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North paid | |
| 2 | 1001 | North | 250 | Paid | 162.5 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
North paid orders are 250 and 75. Their mean is 162.5. The Open North order of 420 is left out.
2. One condition, SUMIFS-style order
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Paid average | |
| 2 | 1001 | North | 250 | Paid | 136.25 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Paid amounts are 250, 90, 130 and 75. Their mean is 136.25. The average range still comes first.
3. Amounts above a threshold in one region
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North over 100 | |
| 2 | 1001 | North | 250 | Paid | 335 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
North orders above 100 are 250 and 420. The 75 is dropped by the second condition.
Common mistakes
Putting the average range last, as in AVERAGEIF.
Fix: AVERAGEIFS wants the numbers to average first, then each pair.
=AVERAGEIFS(C2:C6,B2:B6,"North").#DIV/0! because no row matched every condition.
Fix: Check each criterion separately with COUNTIFS, or wrap the formula in IFERROR.
Frequently asked questions
AVERAGEIF or AVERAGEIFS?
Both give the same result for one condition. AVERAGEIFS has a consistent order and grows easily to more conditions, so it's a good default.
Can a criterion sit on the same column as the average?
Yes: =AVERAGEIFS(C2:C6,C2:C6,">100") averages the amounts that are themselves above 100.
Related functions
- AVERAGEIFAVERAGEIF returns the mean of the values that meet one condition, such as the average order in one region.
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.
More on AVERAGEIFS
AVERAGEIFS averages the numbers in rows where several conditions are true at once. It is how you ask for the mean of paid orders in one region during one quarter without building a helper column.
The argument order
The range to average comes first, then pairs of a criteria range and its condition. This is the reverse of AVERAGEIF, where the average range is last, and it is the most common cause of wrong results. Every criteria range must have exactly the same shape as the average range, otherwise the result is #VALUE!.
=AVERAGEIFS(D2:D20,B2:B20,"North",C2:C20,"Paid")All conditions are joined with AND logic. A row counts only if every pair matches.
Numeric and date conditions
Use operators inside quotes, and join them to a cell or a function with &. A date window is a good example, because it needs two conditions on the same column:
=AVERAGEIFS(D2:D20,A2:A20,">="&DATE(2026,1,1),A2:A20,"<"&DATE(2026,4,1))Using DATE keeps the condition independent of regional date formats. Note that the upper limit is less than the first day of the next quarter, which also works when dates carry a time of day.
What happens when nothing matches
If no row passes all the tests, the result is #DIV/0!. The same happens when the matching cells in the average range contain only text or are blank. Empty cells in the average range are skipped, while 0 is counted.
Before you trust a surprising result, count the rows with COUNTIFS using the same conditions. If the count is 0, the problem is in the criteria, such as a trailing space, text stored as a number or a misspelled label. If the count is higher than expected, a condition is too loose.
Tips for tidy formulas
- Put the conditions in cells and refer to them, so you can change the region or the limit without editing the formula.
- Lock the ranges with dollar signs before filling the formula down a summary table.
- A single condition works too, so you can use AVERAGEIFS everywhere and keep one habit.
For simple cases the page for [AVERAGEIF](/functions/averageif) is enough, and the lesson on [conditional counting](/learn/conditional-counting) covers the same criteria syntax.