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

By Ali, Founder, Excel Lessons

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.

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

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

In F2, type:

=COUNTIFS(B2:B8,"Italy")

It counts the Italy bookings.

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 rows where every pair of range and condition holds. It works with just one pair too. In F2, type: =COUNTIFS(B2:B8,"Italy"). It counts the Italy bookings.

    Hint. One pair.

  2. 2

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

    Hint. Both Italy and May must be true.

  3. 3

    In F4, count June bookings of 4+ nights. Shape: =COUNTIFS(range, test, range, test).

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

  4. 4

    In F5, count May bookings not from Italy. Use <> to exclude Italy.

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

  5. 5

    Every pair is joined by and; COUNTIFS has no or. In F6, count May bookings plus June bookings. Shape: =COUNTIFS(range, test)+COUNTIFS(range, test).

    Hint. May count plus June count.

  6. 6

    Same column, two conditions: a trap. Read =COUNTIFS(C2:C8,"May",C2:C8,"Jun") and say what it returns.

    Hint. And, not or.