Lessons Lesson 92

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

By Ali, Founder, Excel Lessons

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.

Read more about this

Where this comes up again

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

AVERAGEIFS takes the numbers first, then range-condition pairs.

In E2, type:

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

E2
Row A B C D E F G
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. 1

    AVERAGEIFS takes the numbers first, then range-condition pairs. In E2, type: =AVERAGEIFS(C2:C7,A2:A7,"North").

    Hint. Minutes come first.

  2. 2

    In E4, use AVERAGEIFS for Ada in the North.

    Hint. Two pairs.

  3. 3

    Cai has no deliveries, so AVERAGEIFS divides by nothing. In E6, wrap Cai's AVERAGEIFS in IFERROR. Show No runs when it errors.

    Hint. Wrap it in IFERROR.

  4. 4

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

    Hint. Same rows, same average.

  5. 5

    In E8, use AVERAGEIFS for North runs past 45 minutes.

    Hint. ">45" as the second criterion.