The AVERAGEIFS function
Returns the average of the cells that meet every one of several conditions. The average range comes first.
Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- average_range
- The cells to average.
- criteria_range1
- The cells the first condition is checked against.
- criteria1
- The first condition.
- criteria_range2, criteria2 (optional)
- More range and condition pairs.
Examples
E1
=AVERAGEIFS(1C2:C6,2A2:A6,3"North",4B2:B6,5"Shoes")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 460 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
In plain words: Average the 1sales in C2:C6 on the rows where 2the region in A2:A6 is 3North and 4the product in B2:B6 is 5Shoes.
E1=AVERAGEIFS(C2:C6,A2:A6,"South",C2:C6,">0")
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 240 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |