To count every occurrence of a term across a range in Excel, put the term in D1 and use =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER(D1),"")))/LEN(D1)), replacing A2:A100 with your text range. This counts matches without regard to capitalization, including matches inside longer words; if you only want to count cells that match or contain the term, use COUNTIF instead.
Excel can count a particular word in several different ways, and choosing the right one matters. You might want the number of times the word appears, the number of cells that contain it, or the number of cells whose entire contents equal it. The steps below show how to do each, how to check the result, and what to change if Excel’s count does not match your expectations.
What You’ll Need
- A worksheet with the text you want to search, such as entries in A2:A100.
- A cell for the word or phrase to count. The examples use D1 so you can change the search term without editing the formula.
- Microsoft Excel for Microsoft 365 on Windows. The main formulas also work in recent desktop versions of Excel; Excel for the web supports them too. Mac users can use the same formulas, though some keyboard shortcuts differ.
Step-by-Step
- Decide what you mean by “count.” Counting occurrences gives the total number of times a term appears in all the text, even if a single cell contains it several times. Counting cells gives the number of rows or cells that contain the term, with each qualifying cell counted once. An exact-match count includes only cells whose entire contents equal the search term. For example, if A2 says “apple and apple,” it has two occurrences but is one cell containing apple.
- Set up the search term and text range. Put the text to search in one column, such as A2:A6, and type the term to count into D1. Use a cell reference for the term rather than typing it into a formula: this makes it easier to reuse the count for another word. Avoid including the header row in the range unless you want Excel to search it too.
- Count every occurrence, including repeated appearances in one cell. With text in A2:A6 and the search term in D1, enter this formula in an empty cell:
=SUMPRODUCT((LEN(A2:A6)-LEN(SUBSTITUTE(LOWER(A2:A6),LOWER(D1),"")))/LEN(D1))
For example, suppose A2 contains “Apple pie and apple juice,” A3 contains “I bought an apple,” A4 contains “Pear tart,” A5 contains “APPLE,” and A6 is blank. If D1 contains apple, the result is 4: A2 has two matches, A3 has one, A5 has one, and the other cells have none. LOWER makes the search case-insensitive. LEN measures the original and replacement text; the difference is divided by the length of the search term to calculate how many times it occurred in each cell. SUMPRODUCT adds those counts together.
This formula finds the text as a sequence of characters, not necessarily as a separate word. Searching for cat would also count cat inside catalog. It also treats a term such as red apple as a phrase and counts that phrase wherever its characters appear consecutively. Make sure D1 is not blank, because dividing by the search-term length would cause an error.
- Count cells that contain the term at least once. If each matching cell should count only once, even when the term occurs several times in it, use COUNTIF with wildcard characters. With text in A2:A6 and the term in D1, enter:
=COUNTIF(A2:A6,"*"&D1&"*")
For the sample text above and apple in D1, the result is 3 because A2, A3, and A5 contain the term. A2 counts once, not twice. The asterisks mean that any text can appear before or after the search term. This is also a substring search, so it can count a cell containing pineapple when the term is apple. COUNTIF is not case-sensitive.
- Count cells that equal the term exactly. To count cells where the whole cell content is the search term, use an exact criterion. With text in A2:A6 and the term in D1, enter:
=COUNTIF(A2:A6,D1)
For example, if A2:A6 contains apple, green apple, apple, Apple, and a blank cell, and D1 contains apple, the result is 3. COUNTIF ignores capitalization, so Apple also matches. It does not match green apple, because that cell contains additional text. Use this method for lists where each cell holds one label, category, name, or other complete value.
- Check the range and test the result. Confirm that the formula refers to the cells holding your data and that D1 contains the intended term without an accidental leading or trailing space. Try a small range with text you can count manually. If you plan to copy the formula elsewhere, consider locking the search-term reference as
$D$1. For example, with text in A2:A6 and the search term in D1, the occurrence formula can use=SUMPRODUCT((LEN(A2:A6)-LEN(SUBSTITUTE(LOWER(A2:A6),LOWER($D$1),"")))/LEN($D$1)). The dollar signs keep the term cell fixed when the formula is copied; they do not lock the text range.
To enter a formula, select the destination cell, type the equals sign and formula, then press Enter. On Windows, F2 edits the selected cell and Ctrl+Z undoes an edit. On a Mac, use Command+Z to undo; the formula syntax is the same. In Excel for the web, type the formula in a cell or the formula bar and press Enter. If your regional settings require semicolons instead of commas between function arguments, use the separator Excel expects in your installation.
Common Mistakes
- Mixing up occurrences and matching cells. The LEN/SUBSTITUTE formula counts each appearance. COUNTIF with wildcards counts each qualifying cell once. If the result seems low, check whether you used COUNTIF when a cell might contain the term several times.
- Assuming a match means a whole word. Both the occurrence formula and the wildcard COUNTIF formula can find a term inside a longer string. A search for art may match artist. Review the source text or choose a method that matches your definition of a word.
- Including the header or unrelated rows. A range such as A:A searches the entire column, including a heading, notes, and possibly other data. Use a range that covers the records you intend to count, such as A2:A500.
- Leaving the search cell empty or misspelling the term. A blank target makes the occurrence formula invalid, while an extra space can make an exact COUNTIF return zero. Check D1 and, if needed, remove unintended spaces from the source or search term.
- Expecting a case-sensitive count. LOWER deliberately makes the occurrence formula case-insensitive, and COUNTIF also ignores case. A count of apple therefore includes Apple and APPLE.
Tips
- Keep the search term in a dedicated cell and give that cell a clear label, such as “Word to count.” You can then change the term and see the updated result without rewriting the formula.
- For a long or regularly updated list, convert the data to an Excel table: select a cell in the data and choose Insert > Table. Tables expand as you add rows, making it easier to keep a count range current. Check the formula after converting the range, because Excel may replace cell references with table column names.
- Use a short test range before applying a formula to thousands of rows. Include one cell with two appearances, one with a different capitalization, one with no match, and—if relevant—one containing the letters inside a longer word.
- If your intended search term contains an asterisk (*) or question mark (?), remember that COUNTIF treats those as wildcard characters. For a literal wildcard character, put a tilde before it in the criterion. The LEN/SUBSTITUTE approach treats the target as text rather than a COUNTIF wildcard pattern.
- For a case-sensitive count of occurrences, remove LOWER from the formula. With text in A2:A6 and the search term in D1, use
=SUMPRODUCT((LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,D1,"")))/LEN(D1)). With D1 set to apple and the sample cells above, this returns 2 because it counts the two lowercase appearances in A2 and A3, not APPLE in A5.
Troubleshooting
- The result is #DIV/0!. The occurrence formula divides by the number of characters in D1. Enter a nonblank search term there, and make sure the reference points to the correct cell.
- The result is zero, but you can see the word. Check that the text range includes the visible cell and that D1 has no extra spaces or different punctuation. For an exact COUNTIF, additional text in the cell prevents a match. For a wildcard COUNTIF, confirm the formula includes an asterisk on both sides of the term.
- The count is higher than expected. Look for the term within longer words, different rows included in the range, or repeated appearances in one cell. The occurrence formula counts each appearance; the wildcard COUNTIF counts each matching cell once.
- The formula appears as text instead of returning a number. Change the destination cell’s number format to General, then edit the formula and press Enter. Also check that the formula begins with = and that an apostrophe was not entered before it.
- Excel rejects the formula. Check that quotation marks are straight double quotes, parentheses are balanced, and your Excel regional settings use the separator you entered. If the workbook contains error values in the searched text range, fix those source cells or test the formula on a clean range.
Frequently Asked Questions
Can Excel count a word across multiple cells?
Yes. Put the cells in one range, such as A2:A100, and use that range in the formula. The LEN/SUBSTITUTE method totals occurrences across the range, while COUNTIF can count matching cells.
Does the formula distinguish uppercase and lowercase?
The main occurrence formula uses LOWER, so it does not. COUNTIF is also case-insensitive. For case-sensitive occurrences, use the version in Tips that omits LOWER.
How do I count a word only when it appears by itself?
COUNTIF with an exact criterion counts cells whose entire content equals the term, but it does not count standalone words within longer sentences. The occurrence formula searches character sequences and can match inside a longer word. If you need whole-word counts inside sentences, check boundaries and punctuation carefully or use a dedicated text-processing method suited to your data.
Can I count a phrase instead of one word?
Yes. Put the phrase in D1 and use the occurrence formula. It counts consecutive matches of that phrase, including repeated appearances in a cell. For the COUNTIF wildcard version, it counts cells containing the phrase at least once.
Can I count only cells that contain the term, not the number of times it appears?
Yes. Use =COUNTIF(A2:A100,"*"&D1&"*") with the text in A2:A100 and the term in D1. Each cell is counted once even if the term appears several times in it.
Will these formulas update when I edit the text?
Yes. Excel recalculates the result when a referenced cell changes, provided calculation is set to Automatic. If the number does not refresh, open Formulas > Calculation Options and select Automatic.
Related
- Convert an Excel Cell to Number: Quick Methods
- Convert Excel to Google Docs: A Step-by-Step Guide
- How to Compare Lists in Excel for Differences
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Grading in Excel Using IF: Formulas and ExamplesTo assign letter grades in Excel, put each numeric score in column B and enter a nested IF…
- Excel CHAR Function Examples for Line Breaks and SymbolsExcel’s CHAR function converts a character code into its corresponding character; for example, with a first name in…
- Unique Excel Formula With Multiple Criteria: How It WorksTo return distinct values that meet multiple conditions in Excel for Microsoft 365, combine UNIQUE with FILTER. With…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
- Excel PMT Function: Calculate Loan PaymentsTo calculate a fixed loan payment in Excel, divide the annual interest rate by the number of payments…
- Excel Days Until End of Month: Formula and ExamplesWith no input cells needed, enter =EOMONTH(TODAY(),0)-TODAY() to calculate the number of days from today to the last…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.