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.
Next with premium:SUMIFSUMIFS
Need help?
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". Google Sheets only understands <>.
The answer
- E1
=COUNTIF(1B2:B8,2"<>Closed")
In plain words: Count the statuses 1in B2:B8 that are 2anything but Closed.