To count how many cells contain a specific word in Excel, use COUNTIF. For cells that exactly match the word, type =COUNTIF(B2:B9,”Yes”). For cells that contain the word anywhere, add asterisks: =COUNTIF(B2:B9,”*Yes*”). To count how many times a word appears, including repeats inside the same cell, use a formula with LEN and SUBSTITUTE, shown below.
Here are the formulas for every common situation, with examples you can copy.
Which formula do you need?
| Goal | Formula |
|---|---|
| Cells that exactly match a word | =COUNTIF(A2:A100,”apple”) |
| Cells that contain a word anywhere | =COUNTIF(A2:A100,”*apple*”) |
| Using a word typed in another cell | =COUNTIF(A2:A100,”*”&D2&”*”) |
| Every time a word appears in one cell | =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),”apple”,””)))/LEN(“apple”) |
| Every time a word appears in a range | =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),”apple”,””)))/LEN(“apple”)) |
| Case-sensitive exact matches | =SUMPRODUCT(–EXACT(A2:A100,”Apple”)) |
| Total words in a cell | =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1) |
How to count cells with a specific word (COUNTIF)
- Click the cell where you want the result.
- Type =COUNTIF(
- Select the range to search, like B2:B9, and type a comma.
- Type the word in quotes, like “Yes”, then type ) and press Enter.
COUNTIF isn’t case-sensitive, so “yes,” “Yes,” and “YES” all count. Extra spaces do matter. “Yes ” with a trailing space won’t match “Yes.” Wrap your data in TRIM or clean it first if you get a lower count than expected.
Count cells that contain a word as part of longer text
Use asterisks as wildcards. =COUNTIF(A2:A100,”*late*”) counts cells like “Late delivery” and “Arrived late.” It also counts “chocolate,” because “late” is inside it. To match the whole word only, see the tip in the next section.
Refer to a cell instead of typing the word
If the word is in cell D2, use =COUNTIF(B2:B9,D2) for exact matches, or =COUNTIF(B2:B9,”*”&D2&”*”) for partial matches. You can then copy the formula down to count several words at once, or use a pivot table to count every distinct value in a column.
How to count how many times a word appears
COUNTIF counts cells, not words. If one cell says “apple, apple, pear,” COUNTIF counts it once. To count every occurrence:
- In one cell: =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),”apple”,””)))/LEN(“apple”)
- In a range: =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),”apple”,””)))/LEN(“apple”))
How it works: SUBSTITUTE removes every copy of the word, and the difference in length divided by the word’s length equals the number of copies. LOWER makes it case-insensitive. Remove LOWER for a case-sensitive count, and type the word exactly as it appears.
Tip: To avoid counting the word inside other words, add spaces around it: use ” apple “ and replace A2 with ” “&A2&” “. This works well for text separated by spaces but not for words next to punctuation.
How to count multiple words at once
- Either word: =SUM(COUNTIF(B2:B9,{“Yes”,”Maybe”}))
- Two conditions in different columns: =COUNTIFS(B2:B9,”Yes”,C2:C9,”Paid”)
How to count all words in a cell or range
For one cell: =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1). For a range, wrap the same idea in SUMPRODUCT, or put the formula in a helper column and add it up with SUM.
Using Google Sheets instead? See counting a word in Google Sheets.
To keep an eye on a result while you edit, use the Excel Watch Window.
Troubleshooting
- Result is 0: Check for typos, extra spaces, or text stored with different spelling. Try the wildcard version.
- #NAME? error: A quote mark is missing around the word, or a function name is misspelled.
- Numbers stored as text: COUNTIF may not match “5” and 5 the same way. Convert the column to one type first.
- Words with wildcards in them: To count a literal asterisk or question mark, put a tilde before it, like “~*”.
Related: Count how many times a word appears in Excel.
Frequently asked questions
Does COUNTIF work in Google Sheets?
Yes. COUNTIF, COUNTIFS, SUMPRODUCT, LEN, and SUBSTITUTE work the same way in Google Sheets.
Can I count words with a filter applied?
COUNTIF counts hidden rows too. To count only visible rows, use a helper column with SUBTOTAL, or use the FILTER function in Excel for Microsoft 365.
Is there a word count button in Excel?
No. Excel doesn’t have a built-in word count like Word does, so you need a formula.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.