Lessons Lesson 32

COUNTIF

About this lesson

COUNTIF counts the cells in a range that meet a condition. The condition can be a word, a number, a comparison in quotation marks, or a cell holding one.

By the end you can

  • count the rows that match a condition with COUNTIF.
  • count with a comparison such as greater than or not equal to.

Practises COUNTIF COUNTIFS SUMIF

By Ali, Founder, Excel Lessons

The idea

Two arguments: where to look and what to look for. There is no third, because a count adds nothing. Everything else follows SUMIF's rules. A word or number matches exactly. A comparison goes inside quotation marks with its operator. A cell can supply the condition. And <> at the front means everything except.

The mistake is the quotation marks around a comparison. COUNTIF(D2:D7,>50) is a syntax error. COUNTIF(D2:D7,">50") is a count. The operator is part of the text.

The mistake to watch for

Leaving the quotation marks off a comparison. COUNTIF(D2:D7,>50) is a syntax error. The same with the marks is a condition. The operator lives inside the quotation marks with the number. And conditions ignore case, so Dog and dog count the same.

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

COUNTIF counts the cells in a range that meet a condition.

In F2, type:

=COUNTIF(B2:B7,"Dog")

It counts the dog appointments.

F2
Row A B C D E F G
1 Date Species Vet Fee
2 2 Jun Dog Ngo 48 Dog visits
3 2 Jun Cat Ngo 39 Fees over 50
4 3 Jun Dog Blake 62 Named vet
5 3 Jun Rabbit Blake 35 Not dogs
6 4 Jun Dog Ngo 55
7 4 Jun Cat Blake 41
8
9 Vet Blake

Every step

  1. 1

    COUNTIF counts the cells in a range that meet a condition. In F2, type: =COUNTIF(B2:B7,"Dog"). It counts the dog appointments.

    Hint. Species column, "Dog".

  2. 2

    Read =COUNTIF(B2:B7,"Cat") and say what it returns.

    Hint. Two rows say Cat.

  3. 3

    A comparison in quotes works as a condition too. In F3, count appointments charged more than 50. Shape: =COUNTIF(range, test).

    Hint. ">50".

  4. 4

    A condition can be a cell reference. In F4, count appointments for the vet in B9. Remember: a cell reference needs no quotes.

    Hint. C2:C7, B9.

  5. 5

    In F5, count appointments for anything other than a dog.

    Hint. "<>Dog".

  6. 6

    Conditions understand wildcards. An asterisk stands for any run of characters. Read =COUNTIF(C2:C7,"N*") and say how many appointments it counts.

    Hint. Starts with N.