AVERAGEIFS

About this lesson

AVERAGEIFS averages the cells in one range where every condition in the pairs that follow holds. The numbers come first and the range-condition pairs after, in the same order as SUMIFS.

By the end you can

average rows that match two conditions.
trap the error AVERAGEIFS gives when nothing matches.

Practises AVERAGEIFS AVERAGEIF SUMIFS IFERROR

The idea

The last of the plural conditional functions, and the one with a sharp edge. When no row matches, SUMIFS says 0 and COUNTIFS says 0. But AVERAGEIFS says #DIV/0!, because there is nothing to divide by. A report column built on AVERAGEIFS needs IFERROR around it wherever the criteria can miss.

The argument order is SUMIFS' order: numbers first, then pairs. It is the reverse of AVERAGEIF's. Write the plural form even for one condition, and the order stops mattering. There is only one to remember.

The mistake to watch for

Expecting 0 for no match. SUMIFS and COUNTIFS return 0 when nothing matches. AVERAGEIFS returns #DIV/0!, because an average of nothing is not zero. Wrap it in IFERROR wherever the criteria can miss. And write the plural form even for one condition, so the argument order never has to be remembered.

Where this comes up again

SUMIFS, and the Argument Order That Flips Conditional Math
AVERAGEIF Conditional Math
SUMIF Conditional Math
COUNTIFS Conditional Math

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, the average delivery time in the North, with AVERAGEIFS: the minutes first, then the region column and the region.

To begin, type it exactly:

=AVERAGEIFS(C2:C7,A2:A7,"North")

Row	A	B	C	E
1	Region	Driver	Minutes	North average
2	North	Ada	42
3	North	Ben	58	Ada in the North
4	South	Ada	35
5	North	Ada	47	Cai in the North
6	South	Ben	66
7	North	Ben	51	North, over 45
8

Every step

1

In E2, the average delivery time in the North, with AVERAGEIFS: the minutes first, then the region column and the region. To begin, type it exactly: =AVERAGEIFS(C2:C7,A2:A7,"North").

Hint. Minutes first.
2

In E4, Ada's average in the North: two conditions.

Hint. Two pairs.
3

Cai has no deliveries, so an average of his runs divides by nothing. In E6, wrap the AVERAGEIFS for Cai in the North in IFERROR with the text No runs.

Hint. IFERROR outside.
4

Read =AVERAGEIF(A2:A7,"North",C2:C7) and say what it returns.

Hint. Same rows, same average.
5

In E8, write a formula using AVERAGEIFS that averages the North deliveries that took more than 45 minutes.

Hint. ">45" as the second criterion.