Excel AVERAGEIFS Function (Average With Multiple Conditions) – Examples & Practice
Practice the Excel AVERAGEIFS function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Calculate the average of C2:C6 for rows where A2:A6 is "South".
Formula Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
- average_range: The cells to average.
- criteria_range1: First criteria range.
- criteria1: First criterion.
- ...: Additional criteria_range / criteria pairs.
What it does
AVERAGEIFS returns the average of cells that meet multiple criteria. It is the multi-filter counterpart to AVERAGEIF.
Excel AVERAGEIFS Function Examples
Two filters
=AVERAGEIFS(C2:C6, A2:A6, "South", B2:B6, "Paul")
Averages C for matching rows.
sales-by-region.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Liam | 320 |
| 3 | South | Mia | 280 |
| 4 | North | Noah | 410 |
| 5 | West | Olivia | 295 |
| 6 | South | Paul | 360 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Align this pattern with SUMIFS for consistent models.
- Use IFERROR for empty matches.
- Prefer tables for growing datasets.
AVERAGEIFS Function Use Cases
- Multi-segment pricing analytics
- Rep + product performance means
- Quality metrics across lines and shifts
- Operational averages with several dimensions
- Teaching paired-criteria patterns
Common mistakes - AVERAGEIFS function not working
- Argument order confusion vs AVERAGEIF
- Unequal paired range sizes
- OR logic attempted inside one AVERAGEIFS without support
- Averaging rates without weights
- Hidden errors inside average_range
FAQ
What is the first argument?
average_range comes first, followed by criteria pairs—like SUMIFS.
Why #DIV/0!?
No rows matched, or the matched set contains no numeric averages.
Can I OR criteria?
Not natively; combine multiple AVERAGEIFS expressions or redesign with helper columns.
AVERAGEIFS vs FILTER+AVERAGE?
FILTER-based averages can be clearer in modern Excel for complex logic.
Does it ignore text in average_range?
Text generally does not contribute to the numeric average; validate your data.
Comparison
| Function | Criteria |
|---|---|
| AVERAGEIFS | Multiple |
| AVERAGEIF | One |
| MEDIAN | No criteria built-in |
Example
=AVERAGEIFS(C2:C6, A2:A6, "South")
Advanced examples
FILTER alternative
=AVERAGE(FILTER(C2:C6, (A2:A6="South")*(B2:B6="Paul"))) can express complex AND logic readably in modern Excel.