Excel practice › Excel functions › AVERAGEIF and AVERAGEIFS
AVERAGEIF and AVERAGEIFS in Excel: average only the rows that match
AVERAGEIF averages the numbers that meet one condition, and AVERAGEIFS does the same with two or more conditions. With delivery zones in B2:B9, vehicles in C2:C9 and minutes in D2:D9, the average time of a bike delivery downtown is =AVERAGEIFS(D2:D9,B2:B9,"Downtown",C2:C9,"Bike").
When no row meets the conditions there is nothing to average, and both functions return #DIV/0!.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
average_range- The cells to average. In AVERAGEIFS it is always the first argument.
criteria_range1- The range tested with
criteria1. Each criteria range has to be the same size and shape asaverage_range. criteria1- The condition for that range:
"Downtown","<=45", a cell likeF2, or an operator joined to a cell,"<="&G2. criteria_range2, criteria2- More pairs, optional, up to 127 in all. A row is averaged only when it meets every one of them.
AVERAGEIF: one condition, and a different order
=AVERAGEIF(range, criteria, [average_range]). The range to test comes first and the numbers to average come last, like SUMIF. Leave average_range out and the cells of range are both tested and averaged.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Zone | Average | |
| 2 | 5101 | Downtown | Bike | 18 | Downtown | 20 | |
| 3 | 5102 | Suburbs | Car | 41 | |||
| 4 | 5103 | Downtown | Car | 26 | |||
| 5 | 5104 | Downtown | Bike | 22 | |||
| 6 | 5105 | Suburbs | Car | 35 | |||
| 7 | 5106 | Airport | Car | 52 | |||
| 8 | 5107 | Downtown | Bike | 14 | |||
| 9 | 5108 | Suburbs | Bike | 47 |
F2 with "Suburbs" and the average is 41.AVERAGEIFS: every condition has to hold
Now the minutes move to the front, and each condition is a pair: a range, then what to look for in it.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Zone | Vehicle | Average | |
| 2 | 5101 | Downtown | Bike | 18 | Downtown | Bike | 18 | |
| 3 | 5102 | Suburbs | Car | 41 | ||||
| 4 | 5103 | Downtown | Car | 26 | ||||
| 5 | 5104 | Downtown | Bike | 22 | ||||
| 6 | 5105 | Suburbs | Car | 35 | ||||
| 7 | 5106 | Airport | Car | 52 | ||||
| 8 | 5107 | Downtown | Bike | 14 | ||||
| 9 | 5108 | Suburbs | Bike | 47 |
#DIV/0!: nothing matched
An average is a sum divided by a count. With no matching row the count is zero, and Excel answers #DIV/0!. The formula isn't broken: there is no average of nothing. No bike has delivered to the airport:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Zone | Vehicle | Average | |
| 2 | 5101 | Downtown | Bike | 18 | Airport | Bike | #DIV/0! | |
| 3 | 5102 | Suburbs | Car | 41 | ||||
| 4 | 5103 | Downtown | Car | 26 | ||||
| 5 | 5104 | Downtown | Bike | 22 | ||||
| 6 | 5105 | Suburbs | Car | 35 | ||||
| 7 | 5106 | Airport | Car | 52 | ||||
| 8 | 5107 | Downtown | Bike | 14 | ||||
| 9 | 5108 | Suburbs | Bike | 47 |
=IFERROR(AVERAGEIFS(D2:D9,B2:B9,F2,C2:C9,G2),"No orders"). Or replace G2 with "Car" to get 52.A misspelled condition gives the same error, because then nothing matches either. Before hiding a #DIV/0! behind IFERROR, check that zero matches is really what you expect.
The two orders, mixed up
AVERAGEIF tests its first range. Written in AVERAGEIFS order, with the minutes first, it looks for the word Downtown among the minutes, finds none, and returns the same #DIV/0!:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Zone | Average | |
| 2 | 5101 | Downtown | Bike | 18 | Downtown | #DIV/0! | |
| 3 | 5102 | Suburbs | Car | 41 | |||
| 4 | 5103 | Downtown | Car | 26 | |||
| 5 | 5104 | Downtown | Bike | 22 | |||
| 6 | 5105 | Suburbs | Car | 35 | |||
| 7 | 5106 | Airport | Car | 52 | |||
| 8 | 5107 | Downtown | Bike | 14 | |||
| 9 | 5108 | Suburbs | Bike | 47 |
=AVERAGEIF(B2:B9,F2,D2:D9) gives 20.One way to stop mixing them up: write AVERAGEIFS even for a single condition. On a list like this one it gives the same average as AVERAGEIF (exercise 1 accepts either), and the range to average is always in the same place, first.
Blank cells and zeros are different things here. Microsoft's page says AVERAGEIF ignores an empty cell in average_range. A cell that holds 0 is a number: it is averaged, and it pulls the result down.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.
In F2, calculate the average delivery time of the orders that went by Car.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Car average | |
| 2 | 5101 | Downtown | Bike | 18 | ||
| 3 | 5102 | Suburbs | Car | 41 | ||
| 4 | 5103 | Downtown | Car | 26 | ||
| 5 | 5104 | Downtown | Bike | 22 | ||
| 6 | 5105 | Suburbs | Car | 35 | ||
| 7 | 5106 | Airport | Car | 52 | ||
| 8 | 5107 | Downtown | Bike | 14 | ||
| 9 | 5108 | Suburbs | Bike | 47 |
Show the answer
=AVERAGEIF(C2:C9,"Car",D2:D9) gives 38.5.
Four orders went by car: (41 + 26 + 35 + 52) / 4 = 38.5. AVERAGEIFS gives the same result with the range to average first: =AVERAGEIFS(D2:D9,C2:C9,"Car").
F2 holds a zone and G2 a number of minutes. In H2, average the delivery times in that zone, counting only the orders that took G2 minutes or less.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Zone | Max min | Average | |
| 2 | 5101 | Downtown | Bike | 18 | Suburbs | 45 | ||
| 3 | 5102 | Suburbs | Car | 41 | ||||
| 4 | 5103 | Downtown | Car | 26 | ||||
| 5 | 5104 | Downtown | Bike | 22 | ||||
| 6 | 5105 | Suburbs | Car | 35 | ||||
| 7 | 5106 | Airport | Car | 52 | ||||
| 8 | 5107 | Downtown | Bike | 14 | ||||
| 9 | 5108 | Suburbs | Bike | 47 |
Show the answer
=AVERAGEIFS(D2:D9,B2:B9,F2,D2:D9,"<="&G2) gives 38.
Suburbs has three orders: 41, 35 and 47 minutes. 47 is over the cutoff, so the average is (41 + 35) / 2 = 38. Column D is both averaged and tested, which is fine.
F2 holds a zone and G2 a vehicle. In H2, return the average delivery time for that pair, or the text No orders when no order matches.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Zone | Vehicle | Minutes | Zone | Vehicle | Average | |
| 2 | 5101 | Downtown | Bike | 18 | Airport | Bike | ||
| 3 | 5102 | Suburbs | Car | 41 | ||||
| 4 | 5103 | Downtown | Car | 26 | ||||
| 5 | 5104 | Downtown | Bike | 22 | ||||
| 6 | 5105 | Suburbs | Car | 35 | ||||
| 7 | 5106 | Airport | Car | 52 | ||||
| 8 | 5107 | Downtown | Bike | 14 | ||||
| 9 | 5108 | Suburbs | Bike | 47 |
Show the answer
=IFERROR(AVERAGEIFS(D2:D9,B2:B9,F2,C2:C9,G2),"No orders") gives No orders.
IFERROR returns the average when there is one, and your text when AVERAGEIFS ends in #DIV/0!. Change G2 to Car and the same formula shows 52.
Questions people ask
Why does AVERAGEIFS return #DIV/0!?
Because no row met all the conditions, so there was nothing to divide by. Check the spelling of each condition and whether the combination exists in the data at all. If an empty result is expected, wrap the formula in IFERROR. Other causes of this error are on the #DIV/0! page.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and AVERAGEIFS one or more. The order differs: AVERAGEIF is (range, criteria, average_range), with the numbers last and optional; AVERAGEIFS is (average_range, criteria_range1, criteria1, ...), with the numbers first.
Does AVERAGEIF ignore blank cells and zeros?
It ignores empty cells in the range being averaged, according to Microsoft's page. It does not ignore zeros: 0 is a number and counts in the average. To leave zeros out, add a condition on the numbers themselves, such as ">0" or "<>0", with AVERAGEIFS.
How do I average the values between two numbers?
Test the same column twice with AVERAGEIFS: =AVERAGEIFS(D2:D9,D2:D9,">=20",D2:D9,"<=40") averages the deliveries that took 20 to 40 minutes. On the list above those are 26, 22 and 35 minutes, and the result is 27.67.