How to Count Cells With Text in Google Sheets
=COUNTIF(range, "*") to count the cells that contain text. To count cells that contain a particular word, wrap it in asterisks: =COUNTIF(range, "*apple*"). COUNTIF is not case sensitive.
Count every text cell
COUNT counts numbers and COUNTA counts everything that is filled in. Neither counts only text. For that, use COUNTIF with a wildcard. In a text criterion, * stands for zero or more characters, so "*" matches text cells.
The example
Here is a list in cells A1:A7. It holds four text entries, one number (42) and one empty cell.
| A | |
|---|---|
| 1 | Item |
| 2 | Apple pie |
| 3 | 42 |
| 4 | Banana |
| 5 | |
| 6 | Pineapple |
| 7 | Apple juice |
=COUNTIF(A2:A7, "*")
This returns 4: Apple pie, Banana, Pineapple and Apple juice. The number 42 and the empty cell are not counted, because they are not text.
Count cells that contain a word
Put asterisks on both sides of the word to match it anywhere in the cell:
=COUNTIF(A2:A7, "*apple*")
This returns 3: Apple pie, Pineapple and Apple juice. The match is not case sensitive, as Google's COUNTIF page notes, so "*apple*" finds Apple. Use "apple*" for cells that start with the word, and "*apple" for cells that end with it. To match a literal * or ?, put a tilde in front of it.
Which function for which count?
| You want to count | Use |
|---|---|
| Cells with numbers | COUNT |
| Cells with anything in them | COUNTA |
| Cells with text | COUNTIF(range, "*") |
| Cells containing a word | COUNTIF(range, "*word*") |
| Cells meeting two conditions | COUNTIFS |
Common mistakes
- Missing quotation marks. A text criterion must be in quotes.
- Using COUNTA and expecting text only. It also counts numbers.
- Cells that look empty. A cell with a space or an empty-string result is text.
- Numbers stored as text. They count as text in a wildcard match.
Try it yourself
Use the list above (A1:A7). Type your formula into the box, starting with =, then press Check.
Exercise 1: All text cells
Write a formula that counts the cells in A2:A7 that contain text.
Exercise 2: Contains a word
Write a formula that counts the cells in A2:A7 that contain the word apple anywhere.
What to learn next
- COUNTIF: count the rows that match a condition.
- COUNT and COUNTA: count numbers or non-empty cells.
- COUNTIFS: count rows that meet several conditions.
- COUNTBLANK: count empty cells.
Syntax and rules in this guide were checked against Google's Count cells with text help page. See all guides.