Excel Practice
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

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.

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: COUNTIF(range, condition), with nothing to add.

In F2, type =COUNTIF(B2:B7,"Dog") to count the appointments that were for dogs.

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: COUNTIF(range, condition), with nothing to add. In F2, type =COUNTIF(B2:B7,"Dog") to count the appointments that were for dogs.

    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 quotation marks works as a condition here too. In F3, count how many appointments were charged at more than 50.

    Hint. ">50".

  4. 4

    The condition can be a cell. The vet named in B9 wants their own figure. In F4, count that vet's appointments, pointing the criterion at B9.

    Hint. C2:C7, B9.

  5. 5

    In F5, write a formula using COUNTIF that counts the appointments for anything other than a dog.

    Hint. "<>Dog".

  6. 6

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

    Hint. Starts with N.