=COUNTIF(A2:A8,"*") counts the cells in A2:A8 that contain text. The asterisk is a wildcard for "any characters", and it only matches text, so numbers, dates, TRUE/FALSE and empty cells are not counted.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Entry | What | Count | |
| 2 | Apple | Text | 3 | |
| 3 | 12 | Not empty | 6 | |
| 4 | Numbers and dates | 2 | ||
| 5 | Pear | Empty | 1 | |
| 6 | 2026-01-05 | |||
| 7 | TRUE | |||
| 8 | n/a |
D2 counts Apple, Pear and n/a: 3. COUNTA counts every filled cell, 6, and COUNT the two numbers (a date is a number). Type 25 into A4 and D3 and D4 go up while D2 stays at 3; type Plum instead and D2 and D3 go up while D4 stays at 2. COUNT and COUNTA are compared on their own page.
A number typed with an apostrophe in front, such as '007, is text, so "*" counts it. To count only cells with at least one character, use "?*": the ? must match one character, so a formula that returns an empty string ("") is never counted.
Count cells that contain specific text
To count cells that contain a word anywhere, put an asterisk on both sides of it. Without the asterisks, the whole cell has to match.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Condition | Count | |
| 2 | Apple | contains apple | 5 | |
| 3 | Pineapple | exactly apple | 1 | |
| 4 | Pear | starts with apple | 3 | |
| 5 | apple juice | word in C6 | 1 | |
| 6 | Plum | juice | ||
| 7 | Green apple | apple, case-sensitive | 3 | |
| 8 | Apple cider |
"*apple*" matches five products. "apple" alone matches only the cell that is exactly Apple (COUNTIF ignores case), and "apple*" the three that start with it. D5 takes the word from C6, so you can type any word there. D7 is case-sensitive: FIND, unlike COUNTIF, tells apple from Apple, so only Pineapple, apple juice and Green apple count. The -- turns the TRUE/FALSE results into 1 and 0 for SUMPRODUCT to add.
If a cell contains text, then return a value
To mark each row, test one cell at a time. SEARCH("apple",A2) returns the position where apple starts, or #VALUE! when it is not there. ISNUMBER turns that into TRUE or FALSE, and IF picks the result.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Contains apple? | Any text? |
| 2 | Apple | Yes | Text |
| 3 | Pear | No | Text |
| 4 | Pineapple | Yes | Text |
| 5 | 42 | No | Not text |
| 6 | Green apple | Yes | Text |
| 7 | 2026-02-01 | No | Not text |
Both columns are filled down. B shows Yes for Apple, Pineapple and Green apple. C uses ISTEXT, which answers a different question: is there text in the cell at all. 42 and the date are numbers, so they are "Not text". Use FIND in place of SEARCH when case matters. More on the two functions, and why SEARCH is the one that ignores case, is on FIND and SEARCH.
=IF(COUNTIF(A2,"*apple*"),"Yes","No") does the same test with COUNTIF: a count of 1 counts as TRUE and 0 as FALSE.
Count cells that contain one of several words
Adding two COUNTIFs counts a cell twice when it contains both words. Test each cell once instead: SEARCH for each word, add the TRUE results, and count the rows where the sum is above 0.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Formula | Count | |
| 2 | Apple | Two COUNTIFs | 5 | |
| 3 | Orange juice | Each cell once | 4 | |
| 4 | Pear | |||
| 5 | apple juice | |||
| 6 | Plum | |||
| 7 | Green apple |
Four products mention apple or juice, but the two COUNTIFs give 5, because apple juice is counted by both. The SUMPRODUCT version gives 4.
Practice: count the text cells
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Delivery | Weight | What | Count | |
| 2 | D-1 | 12 | Notes | ||
| 3 | D-2 | 9 | |||
| 4 | D-3 | damaged | |||
| 5 | D-4 | 15 | |||
| 6 | D-5 | missing | |||
| 7 | D-6 | ||||
| 8 | D-7 | late |
Your turn: Some deliveries have a weight, others a note. Count the cells in B2:B8 that contain text. Write the formula in E2.
Why the count of text cells is off
- Numbers stored as text are counted. A column imported from another system may hold
'125as text."*"counts it, and COUNT does not. If COUNT on a column of numbers is lower than COUNTA, some of them are text; see numbers stored as text. - Cells that only look empty. A cell with a space in it is text and is counted. Use
=SUMPRODUCT(--(LEN(TRIM(A2:A8))>0))to count cells with visible characters. - TRUE and FALSE are not text.
"*"skips them, as it skips error values like#N/A. - COUNTIF ignores case. For a case-sensitive count use FIND inside SUMPRODUCT, as in the second sheet.
Frequently Asked Questions
How do I count cells that contain any text in Excel?
Use =COUNTIF(A2:A8,"*"). The asterisk matches any text, so the formula counts every text cell and skips numbers, dates, TRUE/FALSE and empty cells.
How do I count cells that contain specific text?
Put asterisks around the word: =COUNTIF(A2:A8,"*apple*") counts cells with apple anywhere in them, in any case. Without the asterisks, "apple" counts only cells that are exactly apple.
How do I write "if cell contains text then" in Excel?
Combine IF, ISNUMBER and SEARCH: =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No"). SEARCH returns a position when it finds the text and an error when it does not, and ISNUMBER turns that into TRUE or FALSE.
How do I check if a cell contains any text at all?
Use =ISTEXT(A2), which is TRUE for text and FALSE for numbers, dates and empty cells. Inside IF: =IF(ISTEXT(A2),"Text","Not text").
