Count everything except one value
PremiumIn E1, count the tickets in B2:B8 whose status is anything but Closed. "Not equal to" is <>, so the criterion is "<>Closed".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ticket | Status | Not closed | |||
| 2 | T-101 | Open | ||||
| 3 | T-102 | Closed | ||||
| 4 | T-103 | Waiting | ||||
| 5 | T-104 | Closed | ||||
| 6 | T-105 | Open | ||||
| 7 | T-106 | Closed | ||||
| 8 | T-107 | In progress | ||||
| 9 |
Premium lesson
Unlock COUNTIF and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel assessment (Indeed format)
Why this works
<> means "not equal to". COUNTIF(B2:B8,"<>Closed") counts every status that isn't Closed: two Open, one Waiting and one In progress, 4 in all. It would also count empty cells in the range, so keep the range to the rows that have tickets.
Common mistake: Writing "not equal" the way other languages do, like "!=Closed". Excel only understands <>.
The answer
- E1
=COUNTIF(1B2:B8,2"<>Closed")
In plain words: Count the statuses 1in B2:B8 that are 2anything but Closed.