What it does
It is written like SUMIFS: first the range to average, then pairs of criteria range and criteria. A row is included only if it meets every condition.
For a date range, give the same column twice: ">="&DATE(2025,10,1) and "<"&DATE(2025,11,1). To leave zeros out, you can add the average range itself as a condition: …,D2:D50,"<>0".
If no row meets the conditions, the result is #DIV/0!. With the same conditions, SUMIFS adds and COUNTIFS counts; putting all three side by side is an easy way to see how many rows and which total an average comes from.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)=ÇOKEĞERORTALAMA(ortalama_aralığı; ölçüt_aralığı1; ölçüt1; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
average_range | Required | The cells to average, e.g. the amount column D2:D50. |
criteria_range1 | Required | The range tested by the first condition; the same size as average_range. |
criteria1 | Required | The first condition: "West", ">=1000", a cell or an expression such as ">="&DATE(2025,10,1). Further conditions follow as range and criteria pairs. |
Examples
1Average by region and product
The formula in H2 averages only the rows whose region is West and whose product is Cable.
=AVERAGEIFS(D2:D8,B2:B8,F2,C2:C8,G2)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Product | Amount | Region | Product | Average | |
| 2 | 28-Sep-2025 | West | Cable | 4,200.00 | West | Cable | 6,600.00 | |
| 3 | 2-Oct-2025 | West | Cable | 12,500.00 | ||||
| 4 | 6-Oct-2025 | North | Socket | 8,300.00 | ||||
| 5 | 14-Oct-2025 | West | Socket | 4,750.50 | ||||
| 6 | 21-Oct-2025 | South | Cable | 9,900.00 | ||||
| 7 | 30-Oct-2025 | West | Cable | 3,100.00 | ||||
| 8 | 3-Nov-2025 | North | Cable | 15,200.00 |
Three rows meet both conditions; their average is 6,600.00.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2One rep's average sale in October
The date column is given twice as a condition, and the rep is the third condition. The September and November rows and other reps' sales are left out.
=AVERAGEIFS(C2:C7,A2:A7,">="&DATE(2025,10,1),A2:A7,"<"&DATE(2025,11,1),B2:B7,E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Rep | Amount | Rep | October Average | |
| 2 | 29-Sep-2025 | Emma | 9,800.00 | Emma | 10,166.67 | |
| 3 | 3-Oct-2025 | Emma | 12,500.00 | |||
| 4 | 8-Oct-2025 | Liam | 8,300.00 | |||
| 5 | 16-Oct-2025 | Emma | 7,400.00 | |||
| 6 | 27-Oct-2025 | Emma | 10,600.00 | |||
| 7 | 4-Nov-2025 | Emma | 15,200.00 |
Emma's three October sales average 10,166.67.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Region × product average table
Regions are in column E and products in F1 and G1. The region is given as $E2 and the product as F$1, so each column's formula uses its own header and each row its own region.
=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,F$1)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Amount | Region | Cable | Socket | |
| 2 | West | Cable | 12,000 | West | 10,000 | 4,800 | |
| 3 | West | Socket | 4,800 | North | 10,500 | 5,800 | |
| 4 | North | Cable | 9,600 | ||||
| 5 | West | Cable | 8,000 | ||||
| 6 | North | Socket | 5,400 | ||||
| 7 | North | Socket | 6,200 | ||||
| 8 | North | Cable | 11,400 |
- F2 (filled down to F3)
=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,F$1) - G2 (filled down to G3)
=AVERAGEIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,G$1)
In the West, cable averages 10,000 and sockets 4,800; in the North, cable averages 10,500 and sockets 5,800.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #DIV/0!
Cause: No row meets all the conditions together.
Fix: Check how the conditions are spelled; wrap the formula in IFERROR to show text instead.
- #VALUE!
Cause: One of the criteria ranges is not the same size as the average range.
Fix: Start and end every range on the same rows.
- Average of the wrong column
Cause: Out of AVERAGEIF habit, the average range was put last.
Fix: In AVERAGEIFS the range to average is always the first argument.
Good to know
- When building a region × product summary, use mixed references such as
$E2for the row condition andF$1for the column condition; then the formula can be copied across the whole table. - Add a COUNTIFS with the same conditions to show how many rows each average is based on.
In Nexell
Get Nexell to do it from a sentence
Show the average order values by region and product group in a summary table
Convert to tableBring your order list into the tool and type this. Nexell builds each cell of the summary with conditional average formulas and shows it in the preview first; in the downloaded file, the summary follows any change in the orders.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I average values between two dates with AVERAGEIFS?
Give the date column twice as a condition: =AVERAGEIFS(D2:D50,A2:A50,">="&DATE(2025,10,1),A2:A50,"<"&DATE(2025,11,1)).
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and the average range comes last. AVERAGEIFS takes several conditions and the average range comes first.