If you need to count rows that include a particular word, the function to learn is excel count if cell contains text, a practical use of COUNTIF with wildcards in Microsoft Excel. The idea behind excel count if cell contains text is simple: instead of counting only exact matches, you tell Excel to count any cell whose text includes your search word, wherever it appears inside the cell. That makes it ideal for survey answers, product lists, task notes, feedback comments, and any column where people write free text rather than choosing from a fixed list. This guide explains the formula step by step, shows examples you can copy and adapt, covers case sensitivity, partial matches, multiple conditions, and common errors, and finishes with a checklist for checking your results.
Before diving in, it helps to know what COUNTIF does. COUNTIF counts cells in a range that meet a single criterion. By itself, it counts exact matches, numbers, or comparisons. When you add wildcard characters, asterisks for any sequence of characters, it becomes a flexible text-search counter. That is the core of excel count if cell contains text: the asterisks around your search word tell Excel that characters may appear before and after it. Without those asterisks, Excel looks for a cell that equals the word exactly, which is a different and much narrower count.
Excel Count If Cell Contains Text: The Basic Formula
The basic pattern for excel count if cell contains text is to use COUNTIF with a range and a criterion that wraps the search text in asterisks. For example, to count cells in A2 through A100 that contain the word “apple”, you would write a formula with the range first and the criterion second, where the criterion is the text “apple” surrounded by asterisk wildcards. Excel then counts every cell in that range where “apple” appears anywhere in the text, including cells such as “apple pie”, “green apple”, and “pineapple” if the letters appear in that order. That last example is important: a contains search is about letter sequence, not whole words, so “pineapple” counts for “apple” because the letters are present.
To search for text stored in another cell, replace the typed word with a cell reference joined to the asterisks. For instance, if your search word is in cell D1, you build the criterion by joining an asterisk, the value in D1, and another asterisk. This approach is better than typing the word directly into the formula, because you can change the search word in D1 and watch the count update without editing the formula. It also reduces typing errors and makes the spreadsheet easier for others to understand, since the search term is visible in a cell rather than hidden inside a formula.
| Goal | Formula pattern | What it counts |
|---|---|---|
| Contains a typed word | COUNTIF with asterisks around the word | Cells where the word appears anywhere |
| Contains a word from a cell | COUNTIF with asterisks joined to a cell reference | Same, but the word can be changed easily |
| Starts with a word | COUNTIF with the word followed by an asterisk | Cells beginning with that word |
| Ends with a word | COUNTIF with an asterisk before the word | Cells ending with that word |
| Exact match only | COUNTIF with the word and no wildcards | Cells equal to the word exactly |
A Worked Example You Can Follow
Imagine column A holds customer feedback: “Great service”, “Service was slow”, “Loved the product”, “Great product and service”, and “Delivery was late”. To count feedback mentioning “service”, use excel count if cell contains text with the range covering those cells and the criterion wrapping “service” in asterisks. Excel would count three cells in this small example: the first, second, and fourth, because each contains the letters “service” in order. It would not count “Loved the product” or “Delivery was late”. Try the same idea on your own data, and check the result by filtering the column for the same word to see whether the counted rows match what you expect.
Excel Count If Cell Contains Text: Case, Partial Words, and Spaces
COUNTIF text matching in Excel is normally not case-sensitive, so “Service”, “SERVICE”, and “service” are treated the same. That is convenient for most feedback and survey work, where capitalisation varies. If you truly need a case-sensitive count, COUNTIF alone will not do it; you would need a different approach, such as a helper column with a case-sensitive function, and that is an advanced topic beyond the usual contains count. For most users, the standard behaviour is a benefit, not a limitation, because it avoids missing rows simply because someone typed a capital letter.
Partial-word matching is the other behaviour to understand. As noted, excel count if cell contains text counts letter sequences, not whole words. Searching for “cat” will count “catalogue”, “category”, and “concatenate” as well as “cat” itself. If you need whole-word counting, you must add extra logic, such as checking for spaces around the word, and even then edge cases at the start and end of a cell need care. For many practical tasks, partial matching is exactly what is wanted, for example counting all product names containing a brand fragment. Just be aware of it, and test with a few known rows before trusting a total on a large dataset.
Spaces also matter. A search for “red apple” with a space will only count cells containing that exact phrase with that space. Extra spaces in the data, double spaces, or leading and trailing spaces can cause misses. If your count looks too low, check for inconsistent spacing, and consider cleaning the data with trimming tools before counting. Similarly, punctuation does not stop a contains match, so “apple,” and “apple.” both count for “apple”, because the letters are present regardless of surrounding punctuation.
Excel Count If Cell Contains Text: Multiple Words and Conditions
Often you need more than one condition. For example, you may want to count feedback that mentions “service” in one column and comes from a particular region in another column. For that, use COUNTIFS, the plural version, which accepts multiple range and criterion pairs. Each pair adds a condition, and Excel counts only rows that meet all of them. The contains logic works the same way: wrap the text criterion in asterisks, and pair it with any other criteria you need, such as an exact region name or a date comparison.
If you want to count cells containing either of two words, not both, add two COUNTIF results together. For example, count cells containing “apple” plus cells containing “orange” to get the total mentioning either fruit. Be careful about double counting: a cell containing both words will be counted in both parts, so the sum may exceed the number of distinct rows. If distinct rows matter, you need a different method, such as a helper column that flags rows meeting either condition and then counting the flags. For simple reporting, the added COUNTIFs approach is clear and easy to audit, provided you note the double-count caveat.
Counting Cells That Do Not Contain Text
The reverse question also comes up: how many cells do not contain a word? Excel does not have a single “not contains” wildcard, but you can work it out by subtracting the contains count from the total count of non-blank cells in the range. First count all non-empty cells, then subtract the contains count, and the remainder is the number of non-blank cells without the word. That method is transparent and easy to check. Alternatively, a helper column with a search function can flag each row, and you can count the flags. Choose the method your audience can follow, and document it with a short note next to the formula.
Excel Count If Cell Contains Text: Common Errors and Fixes
Most errors with excel count if cell contains text come from a small set of mistakes. The first is forgetting the asterisks, which turns a contains search into an exact-match search and returns a much lower count. The second is a misspelled range, such as counting A2 to A100 when the data actually runs to A500, which silently misses rows. The third is searching for text that appears as part of a number or a formula result rather than as typed text; COUNTIF can count formula results, but the displayed text and the underlying value must be checked if results look wrong. The fourth is hidden characters, extra spaces, or non-breaking spaces copied from another system, which can break a match that looks correct on screen.
To troubleshoot, start small. Test the formula on five rows where you know the expected count, and confirm it matches. Then expand the range and re-check. Use filtering to view the matching rows and compare them with the count. Check for spelling differences, such as American versus British spellings, and for synonyms that your search word misses. If the count is zero when you expect matches, check the asterisks, the range, and whether the search word is in the correct case for a case-sensitive helper, if you used one. If the count is too high, check for partial-word matches, such as “cat” matching “category”, and refine the criterion if needed.
General spreadsheet guidance on this site follows the same practical style; for example, other how-to guides emphasise testing a formula on a small sample before applying it to a full dataset. That habit saves time and prevents a plausible-looking but wrong total from reaching a report. Keep a note of what each formula counts, especially when you share the file, so a colleague can audit the result without guessing your intent.
Frequently Asked Questions
What is the basic formula for excel count if cell contains text?
Use COUNTIF with your range and a criterion that wraps the search word in asterisks. The asterisks mean any characters may appear before and after the word, so Excel counts cells where the word appears anywhere.
Is the contains count case-sensitive?
No, standard COUNTIF text matching is not case-sensitive, so upper and lower case versions of the same word are counted together. A case-sensitive count needs a different, more advanced approach.
How do I count cells containing either of two words?
Add two COUNTIF contains results together, one for each word. Note that a cell containing both words will be counted twice in that sum, so use a helper column if you need a distinct-row count.
Why does my formula return zero?
Common causes are missing asterisks, a wrong range, a misspelled search word, extra spaces in the data, or data that does not actually contain the letter sequence you searched for. Test on a small known sample first.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF handles one condition, while COUNTIFS handles multiple conditions that must all be true. Use COUNTIFS when you need a contains condition plus another condition, such as a region or a date.
Excel Count If Cell Contains Text: Practice Checklist
A short practice routine makes excel count if cell contains text reliable on real data. First, write the excel count if cell contains text formula on a small test range where you know the answer, and confirm the count before expanding it. Second, change the search word and confirm that excel count if cell contains text updates as expected, which proves the range and wildcards are correct. Third, filter the column for the same word and compare the visible rows with the excel count if cell contains text result, so partial matches and spelling variants become obvious. Readers who also work with related tools may find the guide to counting cells with text in Sheets useful for comparison, and the word-count style guide is a reminder that counting rules differ by tool. Finally, document your excel count if cell contains text formula with a short note stating the range, the search word, and whether partial matches are intended, so anyone reviewing the sheet can audit the excel count if cell contains text result without guessing.
Conclusion
Mastering excel count if cell contains text gives you a fast, reliable way to summarise free-text data without reading every row. The key is the asterisk wildcard: wrap your search word in asterisks for a contains search, use a cell reference for flexibility, and remember that matching is about letter sequence, not whole words, and is normally not case-sensitive. For multiple conditions, move to COUNTIFS; for either-or counts, add COUNTIFs and watch for double counting; for troubleshooting, test on a small sample and filter to verify. With those habits, excel count if cell contains text becomes a dependable tool for surveys, feedback, inventories, and task lists, turning messy text into a clear, checkable number.
