Counting how often a word shows up in Excel depends on what you mean: cells that equal the word, cells that contain it, or every occurrence including repeats within a cell. COUNTIF handles the first two, and a short SUMPRODUCT formula handles the third.
Quick Answer
To count cells containing a word, use =COUNTIF(A2:A100,”*cat*”). For cells that exactly equal it, drop the asterisks. To count every occurrence, use =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),”cat”,””)))/LEN(“cat”)).
How to Count How Many Times a Word Appears in Excel
- Decide whether you want exact matches, cells that contain the word, or every occurrence.
- For exact matches, type =COUNTIF(range,”word”) in an empty cell.
- For cells containing the word anywhere, use wildcards: =COUNTIF(range,”*word*”).
- For total occurrences (a cell with the word twice counts as 2), use the SUMPRODUCT formula from the Quick Answer.
- Point the formula at a cell holding the word, such as “*”&C1&”*”, so you can change the word without editing the formula.
Which Formula to Use
| Goal | Formula |
|---|---|
| Cells equal to the word | =COUNTIF(A:A,”cat”) |
| Cells containing the word | =COUNTIF(A:A,”*cat*”) |
| Every occurrence | SUMPRODUCT with LEN and SUBSTITUTE |
| Case-sensitive count | SUMPRODUCT with EXACT |
Troubleshooting
- The count is too high: wildcards match parts of words, so *cat* also counts wildcat and category.
- The count is zero: check for extra spaces with TRIM, or confirm the range covers your data.
- To find where the word appears, see how to search for a word in Excel.
- Using Google Sheets instead? Read how to count a word in Google Sheets.
Frequently Asked Questions
Is COUNTIF case-sensitive?
No. COUNTIF ignores case. Use SUMPRODUCT with EXACT for case-sensitive counts.
Can I count several words at once?
Add COUNTIF results together, or use COUNTIFS for conditions across columns.
Does this work in older Excel versions?
Yes. COUNTIF and SUMPRODUCT work in every current and older version.
Summary
- Choose exact, contains, or every occurrence.
- Use COUNTIF for exact or contains.
- Use SUMPRODUCT for total occurrences.
- Reference a cell for the search word.
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.