WHAT DOES AVERAGEIFS DO
The AVERAGEIFS function calculates the average of values that meet multiple criteria. It’s an extension of AVERAGEIF and uses AND logic — all criteria must be satisfied.
WHEN TO USE IT
- You need to average values with multiple filters
- Your data is structured in rows with distinct fields
- You want to exclude data that doesn’t meet all conditions
FORMULA SYNTAX
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Description |
|---|---|
average_range | Range containing numbers to average |
criteria_range1 | Range to test against first condition |
criteria1 | The first condition to match |
criteria_range2 | (optional) Another range to test |
criteria2 | (optional) Second condition to match |
EXAMPLE 1: ONE CONDITION
Average all scores where the department is Sales.
| A | B | C | |
|---|---|---|---|
| 1 | Department | Region | Score |
| 2 | Sales | West | 85 |
| 3 | Sales | East | 92 |
| 4 | Marketing | West | 88 |
| 5 | Sales | West | 79 |
=AVERAGEIFS(C2:C5, A2:A5, "Sales")Result: (85 + 92 + 79) / 3 = 85.33
EXAMPLE 2: MULTIPLE CONDITIONS
Average total spend of customers who are located in the West region and have purchased more than $1,000.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Region | Total Spend |
| 2 | Alice | West | 1200 |
| 3 | Bob | West | 900 |
| 4 | Carol | East | 1300 |
| 5 | David | West | 1500 |
=AVERAGEIFS(C2:C5, B2:B5, "West", C2:C5, ">1000")Result: (1200 + 1500) / 2 = 1350
COMMON PITFALLS AND EDGE CASES
- All ranges must be the same size — mismatched ranges return
#VALUE! - If no matches are found, the result is
#DIV/0! - Quotes are required for text or logical criteria:
"Sales",">70" - Blank cells in
average_rangeare ignored
FURTHER READING
Official Microsoft documentation:
AVERAGEIFS – Microsoft Support