Count Words in Excel: Formulas for Exact and Partial Matches

Formulas & functions · Updated November 20, 2026

To count every occurrence of a word across text in Excel, put the word to find in D1 and use =SUMPRODUCT((LEN(LOWER(A2:A100))-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER(D1),"")))/LEN(D1)). This counts matches without regard to letter case, including repeated matches in one cell. If you only need to count cells that contain the word, use COUNTIF instead. The right method depends on whether you want matching cells or individual appearances.

What You’ll Need

  • A Microsoft Excel workbook. These instructions are for Microsoft 365 on Windows; the formulas also work in current Excel for Mac and Excel for the web.
  • The cells containing your text, such as A2:A100, and a cell for the word to search for, such as D1. Using a separate search cell makes it easy to change the word without editing the formula.
  • Text in the cells you plan to count. The examples assume the search word is not blank and the text does not contain errors such as #VALUE!.

Step-by-Step

  1. Decide what you mean by count. There are three common results people want. You can count cells whose entire contents equal a word, count cells that contain the word somewhere, or count every appearance of the word across the text. For example, if one cell says red blue red, it is one cell containing blue but has one appearance of blue and two appearances of red. Choose the result you need before selecting a formula.
  2. Arrange the text and search word. Put the text in one column, with a heading in A1 and data beginning in A2. Put the word you are checking in D1, such as blue. Avoid adding spaces before or after the search word. If the data is in a different location, substitute your actual range and search cell in the formulas below. Keep the range the same size when you edit it; the examples use A2:A100.
  3. Count cells that exactly match the word. Use this when a cell should contain only the search word, rather than a sentence or phrase. With the text in A2:A100 and the search word in D1, enter =COUNTIF(A2:A100,D1). Press Enter. COUNTIF ignores differences in capitalization, so blue and Blue count as matches. It does not count a cell containing blue bird as an exact match. This is a count of matching cells, not a count of repeated appearances within longer text.
  4. Count cells that contain the word or character sequence. If the text in A2:A100 may include other words and the search word is in D1, enter =COUNTIF(A2:A100,"*"&D1&"*"). The asterisks mean that any text may appear before or after the search term. The result counts each qualifying cell once, even if the term appears several times in that cell. This formula is case-insensitive, and it finds the sequence inside longer words too: searching for art can match cart. Use it when that is acceptable, not when you need whole-word boundaries.
  5. Count every appearance across the selected cells. To count repeated matches, use the formula in the opening paragraph. With text in A2:A100 and the search word in D1, enter =SUMPRODUCT((LEN(LOWER(A2:A100))-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER(D1),"")))/LEN(D1)). Press Enter; you do not need Ctrl+Shift+Enter in Microsoft 365. LOWER makes matching case-insensitive. The formula compares the length before and after removing the search sequence, then divides the characters removed by the length of the search word. It counts appearances within longer words and counts separate repetitions in the same cell. Matches are non-overlapping: a character cannot be used in two counted appearances.
  6. Check the calculation with a small example. Suppose A2 contains red blue red, A3 contains blue bird, A4 contains Blue roof, and A5 contains blueblue. Put blue in D1. With this four-cell range and the search word in D1, the occurrence formula is =SUMPRODUCT((LEN(LOWER(A2:A5))-LEN(SUBSTITUTE(LOWER(A2:A5),LOWER(D1),"")))/LEN(D1)). The result is 5: one match in A2, one in A3, one in A4 despite its capital B, and two in A5. The wildcard COUNTIF formula returns 4 because all four cells contain the sequence, but it counts each cell once. This comparison is a useful way to verify that you selected the right kind of count.
  7. Make the result easier to reuse. Put a label such as Occurrences in C1 and the formula in C2, or put the formula next to the data. To search for a different word, change D1; the result updates automatically. If you add rows beyond row 100, extend the range in the formula or convert the list to an Excel table and use its column range. Check that the range starts with the first data row and does not include unrelated notes or totals.
  8. Choose whether capitalization matters. The occurrence formula shown above is case-insensitive because it converts both the text and search word to lowercase. To count only matches with the same capitalization, use the case-sensitive version. With the text in A2:A100 and the search word in D1, enter =SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,D1,"")))/LEN(D1)). For example, if D1 contains blue, this version does not count Blue. SUBSTITUTE is case-sensitive, which is why LOWER is omitted. Keep D1 nonblank with either formula.

Common Mistakes

Counting cells instead of appearances: COUNTIF with wildcards reports how many cells contain a match, not how many times it appears. If a cell contains blue blue, it contributes one to COUNTIF but two to the occurrence formula. Confirm which number your report needs.

Assuming a partial match is a whole word: Wildcards and the length-based formula search for a character sequence. They can find art in cart or blue in bluebird. If you need whole words only, you must define what separates words in your data, such as spaces, commas, or periods, and use a method that treats those separators consistently. A basic wildcard search does not apply word boundaries automatically.

Leaving the search cell blank: The occurrence formula divides by the length of the search term. A blank D1 has a length of zero, so the formula can return a division error. Enter a word before using it. You can also display a blank instead of a result until D1 has a value by wrapping the calculation in an IF formula; see Troubleshooting.

Including the wrong cells: A range that includes a heading, an old result, or a total may inflate the count. Conversely, a range that stops too early misses data. Check the first and last cell references in the formula, especially after copying it to another row.

Expecting COUNTIF to treat punctuation specially: A cell containing blue, and is not an exact match for blue, but a wildcard search can still find the letters. Punctuation, extra spaces, and spelling variations are part of the cell text unless you clean or account for them.

Tips

Keep the search word in one dedicated cell and use a clear label beside the result. That makes the workbook easier to check and lets you test several terms by changing one cell. If you copy the formula elsewhere, consider locking the search cell reference as $D$1 so it does not shift when copied. With text in A2:A100 and the search word in D1, the occurrence formula with a fixed search-cell reference is =SUMPRODUCT((LEN(LOWER(A2:A100))-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER($D$1),"")))/LEN($D$1)).

For a large list of search terms, place one term in each cell in column D and copy the formula down beside them. In that layout, keep the text range fixed as $A$2:$A$100 while allowing the search reference to change from D1 to D2, D3, and so on. Use the fill handle at the bottom-right corner of the selected formula cell to copy it down. Check the first and last copied results to confirm that the references moved as intended.

If the source text has inconsistent spacing, use Excel’s TRIM function in a helper column to remove extra spaces at the start and end and reduce repeated spaces between words. Cleaning data can improve whole-cell comparisons, but it does not make a substring search respect word boundaries. Keep an unchanged copy of important source text before cleaning it, so you can compare the original values if a result looks unexpected.

Troubleshooting

The result is #DIV/0!: The search term is probably blank, or the formula points to an empty cell. Enter a word in D1. If you want the result cell to stay empty until a term is entered, with text in A2:A100 and the search word in D1 use =IF(D1="","",SUMPRODUCT((LEN(LOWER(A2:A100))-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER(D1),"")))/LEN(D1))). This prevents the calculation from running while D1 is blank.

The answer is too high: Check whether the formula counts a sequence inside longer words. A search for sun, for instance, also finds Sunday. Look for repeated terms in individual cells, and verify that you wanted occurrences rather than matching cells. Also confirm the range does not include headings or duplicate data.

The answer is too low or zero: Check for spelling differences, leading or trailing spaces in the search word, and a range that ends before the relevant rows. The case-sensitive formula will exclude differently capitalized text; use the LOWER version if capitalization should not matter. If the search word includes an asterisk or question mark, COUNTIF treats those as wildcard characters; use the occurrence formula or account for the special character when choosing a method.

The formula displays as text or does not update: Make sure the cell is not formatted as Text. Change the format to General, edit the formula, and press Enter. Check that it begins with an equals sign and that the workbook calculation setting is not set to manual. In Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. On Mac, use Formulas > Calculation Options if that command is available in your Excel build; Excel for the web generally recalculates formulas automatically.

Frequently Asked Questions

Can Excel count a word repeated in one cell?
Yes. Use the SUMPRODUCT, LEN, and SUBSTITUTE formula to count each appearance in the selected text range. COUNTIF with wildcards counts that cell only once, no matter how many times the word appears inside it.

Does the formula distinguish uppercase and lowercase?
The occurrence formula with LOWER does not distinguish case. The version without LOWER is case-sensitive because SUBSTITUTE matches capitalization. COUNTIF is generally case-insensitive.

Can I count a whole word without matching part of another word?
Not reliably with a simple wildcard or length formula; those look for a sequence of characters. You need a consistent way to identify word boundaries, such as splitting text on known separators, or a data-cleaning approach suited to your punctuation and layout. Decide how to handle hyphens, apostrophes, and commas before counting.

Can I count the word across an entire column?
Yes. You can use a column reference such as A:A, but a bounded range such as A2:A10000 is often more efficient, particularly with the occurrence formula. Make sure the range does not include the formula cell itself, which can cause a circular reference.

Will the formulas work in Excel for Mac or Excel for the web?
COUNTIF, SUMPRODUCT, LEN, SUBSTITUTE, LOWER, and IF are available in current versions of Excel for Windows, Mac, and the web. The formulas use commas between arguments, as required by the settings assumed here. Some regional settings use semicolons instead; if Excel rejects a formula, check the argument separator used by your installation.

Why does COUNTIF with asterisks return one when a word appears several times?
COUNTIF evaluates each cell as a yes-or-no match and adds one for each cell that qualifies. It does not total the number of matches inside a cell. Switch to the SUMPRODUCT occurrence formula when you need that total.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.