How to Use COUNTIF in Google Sheets
=COUNTIF(range, criterion).
What COUNTIF does
COUNT only counts numbers. COUNTIF counts the cells that meet a test you set, whether the cells hold text or numbers. You use it when you want to know how many times something appears: how many orders are open, how many people are on a team, how many scores are above a target.
COUNTIF syntax
=COUNTIF(range, criterion)
range: the cells that are tested.criterion: the test to apply, such as"Done"or">4".
COUNTIF supports one criterion. To test several conditions at once, use COUNTIFS. Matching is not case sensitive, so "done" and "Done" count the same cells.
A step-by-step example
Here is a small task list in cells A1:D8.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Owner | Status | Hours |
| 2 | Write report | Ann | Done | 3 |
| 3 | Fix bug | Ben | Open | 5 |
| 4 | Send invoice | Ann | Done | 2 |
| 5 | Plan trip | Cara | Open | 6 |
| 6 | Update docs | Ben | Done | 4 |
| 7 | Review PR | Ann | Open | 1 |
| 8 | Test app | Ben | Done | 8 |
To count the tasks with the status Done:
=COUNTIF(C2:C8, "Done")
This returns 4. Here is why:
- Sheets checks each cell in
C2:C8and finds the ones equal to"Done". - Those are in rows 2, 4, 6 and 8.
- It counts them: 4.
A text criterion goes in quotation marks.
Use comparison operators
For numbers, put an operator in front of the value inside the quotation marks. To count the tasks that take more than 4 hours:
=COUNTIF(D2:D8, ">4")
This returns 3 (the 5, 6 and 8 hour tasks). Google's help page lists these operators: =, >, >=, < and <=. A plain number such as 4 checks for equality.
To compare with a value in a cell, join the operator and the reference with an ampersand. If F1 holds 4, then =COUNTIF(D2:D8, ">"&F1) gives the same result.
Use wildcards for partial matches
In a text criterion, ? matches any single character and * matches zero or more characters. To count the tasks whose name starts with "Re":
=COUNTIF(A2:A8, "Re*")
This returns 1 (Review PR). To match a literal ?, * or ~, put a tilde in front of it, as in "~?".
Common COUNTIF mistakes
- Forgetting quotation marks. A text criterion must be in quotes:
"Done", notDone. - Hidden spaces. A cell holding
"Done "(with a trailing space) does not equal"Done". Clean the data withTRIM. - Needing two conditions. COUNTIF takes one criterion. Use COUNTIFS when you need, for example, Ann and Done.
- Counting blanks or non-blanks. For those, COUNTBLANK and COUNTA are simpler.
Try it yourself
Use the task list above (A1:D8). Type your formula into the box, starting with =, then press Check.
Exercise 1: Count by status
Write a formula that counts how many tasks have the status Done (column C).
Exercise 2: Count long tasks
Write a formula that counts how many tasks take more than 4 hours (column D).
What to learn next
- SUMIF: add up the rows that match a condition.
- VLOOKUP: fetch one value from a table.
- QUERY: filter, group and sort with one formula.
- Count cells with text: count text cells and partial matches.
- SUMIF with multiple criteria: AND, OR and between-two-values sums.
- COUNTIFS: counts with several conditions at once.
Syntax and rules in this guide were checked against Google's COUNTIF help page. See all guides.