SUMIF and COUNTIF in Excel, practised on real data

SUMIF adds up only the rows that match a condition. COUNTIF counts them. SUMIFS takes more than one condition. Together they answer most "how much of this kind" questions without a filter or a pivot table. The sheet above is the lesson; this page explains it, with an example worked out by the same formula engine.

What they do

=SUMIF(range, criteria, [sum_range])

range

The column to check, such as the stand or the region.

criteria

What a row must match: text in quotes ("North"), a cell that holds it (G4), or a comparison in quotes (">=100").

sum_range

The column to add up, row for row alongside the first. Leave it out and SUMIF adds the checked column itself, which only makes sense when that column holds the numbers.

=COUNTIF(range, criteria) is the same without the adding: it counts the matching rows.

A worked example

This sheet comes from the lesson, set in the Football club: sales, with the stand and the volunteer for each, and a column of questions on the right.

In G2, =SUMIF(B2:B10,"Gate",D2:D10) checks column B and adds column D on the rows that match: 378. In G3, =COUNTIF(B2:B10,"Gate") counts those rows: 4.

The condition in a cell

Typing the condition into the formula works, but then the formula answers one question forever. Point at a cell instead and the answer follows whatever the cell says: with Bar in G4, =SUMIF(B2:B10,G4,D2:D10) gives 129. Type another stand into G4 and it updates. The lesson checks that you pointed at G4, not typed the name.

Comparisons

A condition can compare. The comparison goes in quotes as text: =COUNTIF(D2:D10,">=125") counts the sales of at least that size and gives 5. To compare with a number in a cell, join the two with &: ">="&H2. Without the quotes Excel can't read it as a condition.

Text conditions can use wildcards: * stands for any run of characters, ? for one, so "N*" matches everything starting with N. Capitals are ignored.

Two conditions: SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

SUMIFS adds up the rows where every condition holds. Its arguments come in a different order from SUMIF: what to add comes first, then pairs of where to look and what to match. Mixing up the two orders is the most common SUMIFS error. Here, =SUMIFS(D2:D10,B2:B10,"Gate",C2:C10,"Luuk") gives 147.

Leaving out the second pair gives the plain SUMIF answer again, 378, too much. The lesson names that mistake. COUNTIFS works the same way for counting, and AVERAGEIF and AVERAGEIFS for averages.

The lesson, step by step

1. 1 Add up only Gate

   In G2: =SUMIF(B2:B10,"Gate",D2:D10) gives 378.

2. 2 Count them

   In G3: =COUNTIF(B2:B10,"Gate") gives 4.

3. 3 Put the question in a cell

   In G5: =SUMIF(B2:B10,G4,D2:D10) gives 129.

   Checked too: Right total, but point at G4 so it changes when G4 does.

4. 4 Count by size

   In G6: =COUNTIF(D2:D10,">=125") gives 5.

5. 5 Two conditions

   In G7: =SUMIFS(D2:D10,B2:B10,"Gate",C2:C10,"Luuk") gives 147.

   Named mistake: =SUMIF(B2:B10,"Gate",D2:D10) gives 378. That's every Gate sale. SUMIFS adds the second condition: volunteer is Luuk.

   Named mistake: =SUMIFS(B2:B10,"Gate",D2:D10,C2:C10,"Luuk"). SUMIFS starts with what to add, D2:D10. Then the pairs: B2:B10 with "Gate", and C2:C10 with "Luuk".

In real Excel

All of these work in every current version of Excel, on Windows and Mac. A condition that matches nothing gives 0, not an error, so check the spelling when a total is unexpectedly 0: a trailing space in the data ("North ") is enough to miss every row. The ranges must be the same size; a SUMIFS with ranges of different lengths gives #VALUE!.

Keep going

1. 7
   Next up is Lookups: VLOOKUPFind a code in a table and bring back its row, with VLOOKUP and XLOOKUP.
2. 5
   Decisions: IF functionLet the sheet decide: IF for one condition, AND when both must hold.
3. 7
   Lookups: VLOOKUPFind a code in a table and bring back its row, with VLOOKUP and XLOOKUP.