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!.

Skip to the 3 exercises ↓

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 as average_range.
criteria1
The condition for that range: "Downtown", "<=45", a cell like F2, 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.

Live exampleAVERAGEIF: average minutes for the zone in F2
G2fx
ABCDEFG
1OrderZoneVehicleMinutesZoneAverage
25101DowntownBike18Downtown20
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47

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.

Live exampleAVERAGEIFS: the zone in F2 and the vehicle in G2
H2fx
ABCDEFGH
1OrderZoneVehicleMinutesZoneVehicleAverage
25101DowntownBike18DowntownBike18
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47

#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:

Live example#DIV/0!: no row meets both conditions
H2fx
ABCDEFGH
1OrderZoneVehicleMinutesZoneVehicleAverage
25101DowntownBike18AirportBike#DIV/0!
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47

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!:

Live example#DIV/0!: the ranges swapped in AVERAGEIF
G2fx
ABCDEFG
1OrderZoneVehicleMinutesZoneAverage
25101DowntownBike18Downtown#DIV/0!
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47

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.

Exercise 1One condition

In F2, calculate the average delivery time of the orders that went by Car.

F2fx
ABCDEF
1OrderZoneVehicleMinutesCar average
25101DowntownBike18
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47
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").

Exercise 2A zone and a cutoff, both in cells

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.

H2fx
ABCDEFGH
1OrderZoneVehicleMinutesZoneMax minAverage
25101DowntownBike18Suburbs45
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47
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.

Exercise 3When nothing matches

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.

H2fx
ABCDEFGH
1OrderZoneVehicleMinutesZoneVehicleAverage
25101DowntownBike18AirportBike
35102SuburbsCar41
45103DowntownCar26
55104DowntownBike22
65105SuburbsCar35
75106AirportCar52
85107DowntownBike14
95108SuburbsBike47
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.

Keep going

Lesson 5 is seven drills on a sales log: COUNTIF, SUMIF, SUMIFS, COUNTIFS and a conditional average, ending on the average of one product's sales. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

SUMIFS function, with live examplesCOUNTIFS function, with live examplesSUMIF function, with live examplesIFERROR and IFNA, with live examples#DIV/0! error in Excel: meaning and fixesSUMIFS and COUNTIFS practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: AVERAGEIFS function, Microsoft Support: AVERAGEIF function. Last checked November 2026.