Excel track · Exercise 16 of 86
COUNTIF: count one condition
Count the rows that meet one condition, and get the edges of the range right.
Beginner
Support ticket log
Read-only data · screen 1 of 3 · answer in B10
In B10, count how many tickets have the status "Open".
| A | B | |
|---|---|---|
| 1 | Ticket | Status |
| 2 | T-1001 | Open |
| 3 | T-1002 | Closed |
| 4 | T-1003 | Open |
| 5 | T-1004 | Pending |
| 6 | T-1005 | Open |
| 7 | T-1006 | Closed |
| 8 | T-1007 | Open |
| 9 | T-1008 | Escalated |
| 10 | Open tickets |
Turn filters on for the ticket table without leaving the keyboard.
Press it, or type it in the bar.
Widen the Status column to fit its longest value, from the ribbon.
- Alt
- H
- O
- I
Press the keys in order, or type them in the bar.
Reference answer
What it does
COUNTIF reads a range and counts the cells that meet one condition. Text criteria go in quotes, so =COUNTIF(B2:B9,"Open") returns 4: four of the eight statuses read Open. A number needs no quotes, while ">=5" and "<>Closed" keep theirs because a comparison is still text.
This sheet holds eight tickets with four different statuses, and the answer sits under the last of them. The range has to reach B9. Stop at B8 and the count comes back as 3, one short, and the ticket you missed is not marked.
Case does not matter, so "open" and "Open" land in the same count. Wildcards work inside the criteria as well: "O*" also returns 4 here, and "T-10*" tested against column A would count all eight tickets.
Syntax
=COUNTIF(range, criteria)Worked example
On Support ticket log, =COUNTIF(B2:B9,"Open") returns 4.
=COUNTIF(B2:B9,"Open")Common errors
| Error | What it means and how to fix it |
|---|---|
#NAME? | An unquoted criterion such as COUNTIF(B2:B9,Open) sends Excel looking for a name called Open, and there is none. |
One short | A range that stops at B8 counts three Open tickets and reports nothing wrong. |
#REF! | The range points at cells that have been deleted since, so there is nothing left to count. |
At work
Support queues and stock rooms run on this count: how many tickets are open, how many lines are short. It is just as often tallied by eye, one screen at a time.