Excel Practice
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

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.

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 what you know.

In E2, type =SUMIF(A2:A8,"Dara O",D2:D8) to total Dara's hours: test the names, match Dara, add 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 what you know. In E2, type =SUMIF(A2:A8,"Dara O",D2:D8) to total Dara's hours: test the names, match Dara, add the hours.

    Hint. Names, "Dara O", hours.

  2. 2

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

    Hint. Hours first.

  3. 3

    Now the reason SUMIFS exists: a second pair. In E4, total Dara's hours in week W2 only. Add the weeks column and W2 as a second pair.

    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 as the range added, once tested. In E6, total the Harbour hours from the entries longer than 20 hours. Test ">20" against the hours themselves.

    Hint. D2:D8 twice.

  6. 6

    A condition can also exclude. "<>Harbour" means anything other than Harbour. In E7, write a formula using SUMIFS that totals the hours booked to anything other than Harbour.

    Hint. "<>Harbour".