Average with two conditions with AVERAGEIFS
PremiumIn F1, find the average sale of Shoes in the North region. AVERAGEIFS starts with the range to average, then each range with its condition.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Avg. North shoes | |||
| 2 | North | Shoes | 500 | ||||
| 3 | South | Shoes | 300 | ||||
| 4 | North | Bags | 250 | ||||
| 5 | North | Shoes | 420 | ||||
| 6 | South | Bags | 180 | ||||
| 7 | North | Bags | 150 | ||||
| 8 | South | Shoes | 610 | ||||
| 9 | North | Shoes | 90 | ||||
| 10 |
Premium lesson
Unlock AVERAGEIF and AVERAGEIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel assessment (Indeed format)
Why this works
Three rows are North and Shoes: 500, 420 and 90. Their average is 1,010 / 3 = 336.67. AVERAGEIFS takes its arguments in the same order as SUMIFS: what to average first, then pairs of range and condition.
Common mistake: Putting the average range last, as in AVERAGEIF. In AVERAGEIFS it comes first.
The answer
- F1
=AVERAGEIFS(1C2:C9,2A2:A9,3"North",4B2:B9,5"Shoes")
In plain words: Average 1the sales in C2:C9 on the rows where 2the region in A2:A9 is 3North and 4the product in B2:B9 is 5Shoes.