SUMIF, COUNTIF and SUMIFS: Totals for Each Group

Excel for Jobs: Formulas, Pivot Tables and Office Skills · Formulas · 4 min read · Updated

Add only what matches

  1. SUMIF looks down one column, here the Region column, for rows that meet a condition.
  2. The condition is North. Rows with a different region are skipped.
  3. For each matching row, it takes the number from the amount column in the same row.
  4. It adds them up: 1200 + 2800 + 2400 + 1400 = 7800.

Totals for the whole sheet are useful, but managers ask more precise questions. How much did the North region sell? How many orders did Ben handle? What was the total for Notebooks in the South? The IF family of functions answers these in one cell, without sorting or filtering. They are some of the most used functions in real offices.

We use the sales table from earlier, with Amount in column G. If you did not finish the Amount column, fill it with =D2*E2 first.

SUMIF: add numbers that meet a condition

=SUMIF(range_to_check, condition, range_to_add)

To get the total sales of the North region:

=SUMIF(C2:C11, "North", G2:G11)     -> 7800

Excel looks down C2:C11 for rows where the region equals North, and adds the matching amounts from G2:G11. The North rows are 1200, 2800, 2400 and 1400, which add up to 7800. The ranges must have the same size. You can put the condition in a cell instead of in quotes. If I1 contains South, use =SUMIF(C2:C11, I1, G2:G11), and change I1 to see another answer.

Similarly, per staff member and per item:

=SUMIF(F2:F11, "Ben", G2:G11)         -> 7750
=SUMIF(B2:B11, "Notebook", G2:G11)    -> 5400

Conditions with signs

A condition can be a comparison, written as text in quotes.

=SUMIF(G2:G11, ">=2000")        -> adds the amounts of 2000 or more
=SUMIF(D2:D11, "<5", G2:G11)    -> amounts where Qty is below 5
=SUMIF(B2:B11, "<>Mouse", G2:G11) -> everything except mouse sales

When you leave out the last argument, SUMIF adds the cells it is checking. The first example adds 2000, 2800, 2800, 2400 and 2400 for a total of 12400. The other two are the same shape and are easy to test yourself.

Wildcards

In conditions, * stands for any text and ? for one character. =SUMIF(B2:B11, "Note*", G2:G11) totals every item starting with “Note”. "*pack" finds items ending in pack.

COUNTIF: count the matching rows

=COUNTIF(C2:C11, "North")     -> 4
=COUNTIF(G2:G11, ">=2000")    -> 5
=COUNTIF(F2:F11, "Ben")       -> 4

Four sales were in the North, five sales were 2000 or more, and Ben handled four sales. COUNTIF is also the quick way to find duplicates: =COUNTIF(A:A, A2) returns how many times the value in A2 appears. If it is more than 1, it is a duplicate.

AVERAGEIF

=AVERAGEIF(C2:C11, "North", G2:G11)    -> 1950

The average North sale: 7800 divided by 4 equals 1950.

SUMIFS, COUNTIFS and AVERAGEIFS: more than one condition

When you need several conditions together, add an S to the name. All conditions must be true. Notice that in SUMIFS the range to add comes first. This is the opposite of SUMIF, and a common source of mistakes.

=SUMIFS(G2:G11, C2:C11, "South", B2:B11, "Notebook")   -> 2400
=COUNTIFS(C2:C11, "North", G2:G11, ">=2000")           -> 2
=AVERAGEIFS(G2:G11, F2:F11, "Ben", C2:C11, "South")    -> 1937.5

In the first example, the only South Notebook row is row 10, which is 2400. The second asks for North sales of 2000 or more, and there are two (2800 and 2400). In the third, Ben's four sales are all in the South: 2000, 1950, 1400 and 2400, with a total of 7750 and an average of 1937.5.

Build a small summary table

These functions make a neat report. On a new sheet called Summary, list the regions in A2:A4 (North, South, East) and write in B2:

=SUMIF(Sales!$C$2:$C$11, A2, Sales!$G$2:$G$11)

Copy it down. The dollar signs keep the ranges fixed while A2 moves to A3 and A4. You get North 7800, South 7750 and East 4600, which together equal the total of 20150. Always check that the parts add up to the total, so you can be sure that no rows are missed because of a spelling difference.

Why a total might be missing rows

  • A region is spelled “Noth” or has a trailing space. The IF family needs an exact match apart from capitals.
  • Numbers stored as text in the range to add (lesson 2).
  • The ranges are different sizes or do not line up.
  • New rows were added below the range. Use a Table (lesson 10), which grows automatically, or whole-column references.

Your task

  1. Build a Summary sheet with totals per region, per staff member and per item using SUMIF.
  2. Add counts with COUNTIF for each region.
  3. Use SUMIFS to find the total for Pen packs in the North (2400).
  4. Check that your regional totals add up to 20150.

How to check it worked

  • North = 7800, South = 7750 and East = 4600.
  • By staff: Asha = 7800, Ben = 7750 and Chidi = 4600.
  • By item: Notebook = 5400, Pen pack = 4400, Backpack = 4200, Desk lamp = 1950 and Mouse = 4200.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF has one condition and takes the range to add last. SUMIFS allows many conditions and takes the range to add first.

Why does SUMIF give 0 when I can see matching rows?

The text probably differs slightly, for example by a trailing space or a spelling difference, or the numbers are stored as text. Clean the data and try again.