Lessons Lesson 31

SUMIFS, and the Argument Order That Flips

About this lesson

SUMIFS adds the cells in one range where every condition in the pairs that follow holds. The range to add is named first. Each condition is joined by and.

By the end you can

  • total rows that match two or more conditions with SUMIFS.
  • say why SUMIFS puts the numbers first and SUMIF puts them last.

Practises SUMIFS SUMIF COUNTIFS AVERAGEIFS

By Ali, Founder, Excel Lessons

The idea

The plural form takes the range to add first, then as many range-and-condition pairs as you need. That is the reverse of SUMIF. The reversal has a reason: with up to 127 conditions allowed, the one argument that appears exactly once has to sit somewhere fixed.

Every pair is joined by and. There is no way to ask for W1 or W2 in one call. So the right answer to an or-question is two SUMIFS added together. Excluding is easy, though. A condition beginning <> means everything except.

The mistake to watch for

Carrying SUMIF's order into SUMIFS. SUMIFS puts the range to add first, then pairs of range and condition. That is the opposite of SUMIF. The wrong order tests the wrong column and returns 0 with no error. Write the plural form for everything, and the order stops mattering.

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

Start with SUMIF, from the last lesson.

In E2, type:

=SUMIF(A2:A8,"Dara O",D2:D8)

It tests the names, matches Dara, and adds the hours.

E2
Row A B C D E F G
1 Employee Week Project Hours
2 Dara O W1 Harbour 22 Dara, SUMIF
3 Dara O W2 Harbour 18 Dara, SUMIFS
4 Sam L W1 Harbour 31 Dara in W2
5 Sam L W2 Ferry Road 24 Dara, W2, Harbour
6 Dara O W2 Ferry Road 12 Harbour over 20
7 Nia P W1 Ferry Road 27 Everything but Harbour
8 Nia P W2 Harbour 9

Every step

  1. 1

    Start with SUMIF, from the last lesson. In E2, type: =SUMIF(A2:A8,"Dara O",D2:D8). It tests the names, matches Dara, and adds the hours.

    Hint. Names, "Dara O", hours.

  2. 2

    SUMIFS does the same job, order flipped. The range to add comes first, then range-condition pairs. In E3, type: =SUMIFS(D2:D8,A2:A8,"Dara O"). It gives the same 52.

    Hint. Hours first.

  3. 3

    Now the reason SUMIFS exists: a second pair. In E4, total Dara's hours in W2 only. Shape: =SUMIFS(sum_range, range, test, range, test).

    Hint. A second pair: B2:B8, "W2".

  4. 4

    Read =SUMIFS(D2:D8,A2:A8,"Dara O",B2:B8,"W2",C2:C8,"Harbour") and say what it returns: Dara, in W2, on Harbour.

    Hint. One row matches all three.

  5. 5

    A condition can be a comparison. A range can appear twice: once added, once tested. In E6, total Harbour hours over 20. Shape: =SUMIFS(sum_range, range, test, sum_range, test).

    Hint. D2:D8 twice.

  6. 6

    A condition can also exclude. "<>Harbour" means anything other than Harbour. In E7, total the hours not booked to Harbour.

    Hint. Not equal to Harbour.