To count cells that exactly match a word, use =COUNTIF(A2:A100,D1); to count cells that contain it anywhere, use =COUNTIF(A2:A100,"*"&D1&"*"). Put the word you are looking for in D1 and the text to check in A2:A100. These formulas are not case-sensitive. If you mean every occurrence of a word, rather than the number of cells containing it, use a different formula described below.
What You’ll Need
- A Microsoft Excel workbook, using Excel for Microsoft 365 on Windows for these instructions. The formulas also work in current Excel for Mac and Excel for the web.
- A column or range containing the text you want to search, such as A2:A100.
- A cell for the search word, such as D1. Keeping the word in its own cell makes it easy to change and reuse the formula.
The examples assume the text is in column A and the search word is in D1. Replace those references with the range and cell used in your workbook. If you use a regional Excel setting that expects semicolons between function arguments, Excel may adjust the formula separators automatically when you enter a formula.
Step-by-Step
-
Decide what you want to count. Excel can count cells whose entire contents equal the search word, cells that contain the word as part of longer text, or the total number of times the text occurs. Those are different questions. For example, if the cells contain “pear,” “pear tart,” and “spears,” an exact-cell count includes only “pear”; a contains count includes all three because each has the letters pear in it; an occurrence count counts appearances, including repeated appearances in one cell.
-
Identify the text range and enter the word to find. Keep the cells to be checked in one range where possible, such as A2:A100. Type the search term into D1, without extra spaces before or after it. For a small list, you can type the word directly into a formula, but a separate search cell is easier to update. Check that the range does not include the result cell, or the formula may include itself in its own count.
-
Count cells that exactly match the search word. With the text in A2:A100 and the word in D1, enter this formula in an empty cell outside that range:
=COUNTIF(A2:A100,D1)For example, if D1 contains pear and the range contains pear, Pear, pear tart, and apple, the result is 2. COUNTIF ignores letter case, so pear and Pear both match. It requires the entire cell value to match; pear tart does not count. This is the best starting point when each cell is meant to hold one label, name, category, or other single value.
-
Count cells that contain the word within longer text. If the word may appear alongside other text, use asterisks as wildcards. With text in A2:A100 and the search word in D1, enter:
=COUNTIF(A2:A100,"*"&D1&"*")The asterisk matches any number of characters before or after the search term. For example, if D1 contains pear and the range has pear, pear tart, and apple, the result is 2. This counts a cell once even if the search term appears several times in that cell. It also finds the letters inside a longer word: searching for art can match partial text such as party. Use this formula when that broader match is acceptable.
-
Count occurrences, including repeats in a cell. If the text is in A2:A100 and the search word is in D1, enter:
=SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER(D1),"")))/LEN(D1))This counts non-overlapping appearances and ignores case. For example, if D1 contains red and the checked cells contain “red red,” “Red,” “shredder,” and a blank, the result is 4: two in the first cell, one in the second, and one in shredder. This counts character sequences, not necessarily separate whole words. Make sure D1 is not blank, because dividing by the length of an empty search term cannot produce a meaningful count.
-
Make the count case-sensitive if necessary. COUNTIF treats uppercase and lowercase as the same. To count cells whose entire contents match D1 with the same capitalization, use this formula with text in A2:A100 and the search word in D1:
=SUMPRODUCT(--EXACT(A2:A100,D1))If D1 contains Pear, this counts Pear but not pear or PEAR. EXACT checks the entire cell, so this is a case-sensitive exact-cell count, not a case-sensitive search inside sentences. Choose it only when capitalization is important to your data.
-
Check the result against a small sample. Look at several cells you expect to match and several you expect not to match. For a contains count, deliberately check a longer word that includes the same letters; for an exact count, check a cell with extra text. This quick check confirms that the formula is answering the intended question rather than simply returning a plausible number. If your data changes, the count updates when Excel recalculates.
Common Mistakes
Confusing cells with occurrences. The wildcard COUNTIF formula counts each matching cell once. A cell containing “pear pear” still adds only 1. Use the LEN and SUBSTITUTE formula when you need a total of repeated appearances.
Assuming a wildcard count recognizes whole words. A search for cat with asterisks can match category or concatenate because the formula looks for the letters anywhere. COUNTIF has no built-in whole-word option for text inside a sentence. If whole-word boundaries matter, use a method that separates or normalizes text into individual words before counting, or review matches with a more suitable text-processing workflow.
Including spaces in the search term. A space accidentally typed before pear in D1 can change the results. Select D1 and inspect the formula bar, or retype the term. If the source cells contain inconsistent leading or trailing spaces, clean the source data before relying on exact matches.
Searching the wrong range. A range that stops at row 100 will not include later entries. Conversely, using an entire column can slow a workbook when you have many complex formulas. Use a range that covers your data and expand it when new rows are added.
Forgetting that punctuation and wildcard characters matter. A question mark and an asterisk have wildcard meanings in COUNTIF criteria. If your search term itself contains those characters, they may behave as wildcards rather than literal characters. Also, a comma or period attached to a word is part of the cell text for an exact match.
Tips
Keep the search term in a clearly labeled cell, such as D1, and put the result beside a label such as “Matching cells.” This makes the purpose of the count clear to anyone opening the workbook. You can change D1 to check another word without editing the formula.
Use a formatted Excel Table if the list grows regularly. Select the data and choose Insert > Table; when formulas refer to a table column, Excel can extend the calculation as rows are added. For a simple fixed list, an ordinary range such as A2:A100 is perfectly suitable.
Use exact matching for tidy category columns and wildcard matching for descriptions, notes, or comments. If you need case-sensitive matching inside text, the exact-cell formula above is not enough; choose a method designed for that requirement rather than assuming COUNTIF distinguishes capitalization.
On Windows, press Ctrl+F to find a word visually; on Mac, use Command+F. Find is useful for inspecting occurrences, but it is not a live count formula and does not update a result cell. Excel for Mac and the web use the same formulas, though ribbon placement and keyboard behavior can vary slightly.
Troubleshooting
The result is zero, but you can see the word. First decide whether the cell must match exactly or merely contain the term. If it is part of longer text, use the wildcard version. Check D1 for extra spaces and confirm that the formula’s range includes the visible cell. A word followed by punctuation will not match an exact-cell criterion unless the punctuation is also in D1.
The result is higher than expected. The wildcard formula can find the term inside longer words, and it counts different matching cells even if the match is only a substring. Inspect examples such as shorter words embedded in longer ones. If you used the occurrence formula, repeated text within a single cell is counted repeatedly by design.
The formula displays an error. Check that the formula begins with an equals sign, that parentheses and quotation marks are paired, and that D1 is not blank when using the occurrence formula. If Excel rejects commas, your regional settings may require semicolons as separators; use the separator Excel expects. Confirm that the formula is not placed inside the range it is counting.
The count does not change after editing data. Press F9 on Windows to recalculate the workbook; on Mac, use Fn+F9 if the function keys are set to media controls, or use Formulas > Calculate Now. Also check Formulas > Calculation Options and select Automatic if the workbook is set to manual calculation. In Excel for the web, changes generally recalculate automatically.
The result differs from a manual search. Find may be set to search formulas or values, or to match entire cell contents. Those settings do not necessarily mirror a wildcard formula. Compare the exact range and matching rules, and test the formula on a few known cells before drawing conclusions from a large list.
Frequently Asked Questions
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive. Use the EXACT and SUMPRODUCT formula above when you need case-sensitive matches of entire cells.
Can I count a word in one cell rather than a range?
Yes. For a single text cell such as A2 and a search term in D1, use a range containing only A2 in the relevant formula. The result will be 0 or 1 for a cell count, or a larger number if you use the occurrence formula and the word repeats.
Does the asterisk mean any word?
In a COUNTIF criterion, the asterisk is a wildcard for any number of characters, including none. It does not mean “whole word.” That is why the contains formula may match a search term inside a longer word.
How do I count a phrase with a space in it?
Put the complete phrase in D1 and use the exact or wildcard formula according to whether the whole cell must match. The phrase must appear with the same spacing as the source text for a direct text match.
Can I use these formulas in Excel for Mac or Excel for the web?
Yes. The formulas shown work in current Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. Keyboard shortcuts and some ribbon locations can differ, but the counting logic is the same.
What if I want to count whole-word matches in sentences?
COUNTIF with asterisks does not enforce word boundaries. For reliable whole-word counting, first split or clean the text so each word is a separate value, then count exact matches. Decide how punctuation, hyphens, and apostrophes should be treated before preparing the data.
Related
- How to Compare a List of Names in Excel: 10 Methods
- Compare Actual vs Budget in Excel: Step-by-Step
- Combine Quantity of the Same Item in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to…
- VLOOKUP Excel: Extended Uses, Examples, and LimitsVLOOKUP can do more than retrieve one value from a simple list: it can use exact or approximate…
- Excel Formula to Split Text in a Cell: A Practical GuideIn Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character…
- COUNTIF Excel Cell Color: Methods That Work ReliablyExcel’s COUNTIF function cannot count cells by fill color on its own: its criteria are based on cell…
- Excel MID Formula: Extract Text by PositionUse Excel’s MID function to extract a specified number of characters from a text value, starting at a…
- Power Query Filter: How to Filter Rows in ExcelTo filter in Power Query, open the query in Power Query Editor, select the filter arrow in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.