Excel Practice
Lessons Lesson 33

COUNTIFS

About this lesson

COUNTIFS counts the rows where every pair of range and condition holds. An or-question is answered by adding two COUNTIFS together.

By the end you can

  • count rows that match several conditions at once with COUNTIFS.
  • read a criterion from a cell instead of typing it.

Practises COUNTIFS COUNTIF SUMIFS

The idea

Pairs all the way down: range, condition, range, condition. Each pair narrows the rows. A row is counted only if it passes every one. Words, numbers, comparisons in quotation marks and cells all work as conditions. And <> at the front excludes.

What COUNTIFS cannot do is widen. Two pairs on the same column, May and June, ask for a booking that is in both months. That counts zero. An or is two counts added, and it always will be.

The mistake to watch for

Writing an or as two pairs. COUNTIFS joins every pair with and. So May in one pair and June in the next asks for a row that is in both months. It returns 0, which looks like an empty result, not a wrong formula. An or is two COUNTIFS added together.

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

COUNTIFS counts the rows where every pair of range and condition holds. It works with one pair too.

In F2, type =COUNTIFS(B2:B8,"Italy") to count the bookings from Italy.

F2
Row A B C D E F G
1 Guest Country Month Nights
2 Alvarez Spain May 3 From Italy
3 Bianchi Italy May 7 Italy in May
4 Novak Czechia Jun 2 Jun, 4 nights up
5 Ferrari Italy Jun 5 May, not Italy
6 Sousa Portugal Jun 4 May or Jun
7 Rossi Italy May 1
8 Weber Germany Jul 9

Every step

  1. 1

    COUNTIFS counts the rows where every pair of range and condition holds. It works with one pair too. In F2, type =COUNTIFS(B2:B8,"Italy") to count the bookings from Italy.

    Hint. One pair.

  2. 2

    Read =COUNTIFS(B2:B8,"Italy",C2:C8,"May") and say what it returns: from Italy, and in May.

    Hint. Italy and May together.

  3. 3

    In F4, count the June bookings of four nights or more. Test the months column against Jun, and the nights column against ">=4".

    Hint. "Jun" and ">=4".

  4. 4

    In F5, count the May bookings that did not come from Italy, using <> to exclude.

    Hint. "<>Italy" and "May".

  5. 5

    Every pair is joined by and. There is no or. To count the bookings that were in May or in June, count each month and add the two counts. In F6, do that with two COUNTIFS and a plus sign.

    Hint. May count plus June count.

  6. 6

    The trap. Read =COUNTIFS(C2:C8,"May",C2:C8,"Jun") and say what it returns.

    Hint. And, not or.