How to Count the Number of Times a Word Appears in Excel

By Aaron Parson

The COUNTIF function in Excel 2013 and 2010 adds up the number of times a specified search term appears in a range of data and displays the result in a cell. COUNTIF only works when your word appears at most once per cell, so as an alternative for cells containing multiple words, use a formula that combines LEN and SUBSTITUTE to analyze the data.

Count with COUNTIF

Step 1

Click any blank cell on your Excel spreadsheet and type "=COUNTIF(" -- without quotation marks here and throughout. Select the range containing your data to add its location to the formula. Alternatively, type the range manually, such as "=COUNTIF(A1:D12".

Step 2

Type a comma, a double quotation mark and then enter the word to search for. Add a closing quotation mark and a closing parenthesis to complete the formula. COUNTIF is case sensitive, so type the word exactly as it appears in your data.

Step 3

Press "Enter" to calculate the number of word appearances. This word count updates automatically as the data in the selected range changes whether it changes due to manual edits or due to updated information elsewhere in the workbook. To change the formula's range or the search term, select the formula's cell and make the change in the formula bar.

Step 4

Repeat the formula in another cell to count additional groups of words, as COUNTIF only counts words in a contiguous group of cells.

Count Multiple Words Per Cell

Step 1

Type "=SUM(LEN()-LEN(SUBSTITUTE(,"text","")))/LEN("text")" into a cell near your data without external quotation marks.

Step 2

Replace both instances of "" with the range of cells to check and replace both instances of "text" with the text to search for, leaving the quotes around the text.

Step 3

Press "Ctrl-Shift-Enter" to set the formula as an array and calculate the number of word appearances. If you press "Enter" as usual when entering a formula, this formula won't work correctly. Any time you modify the formula, you will need to press "Ctrl-Shift-Enter" again.

Tips & Warnings

  • Make the cell containing the word count self explanatory by adding text after the number. Select the formula and add an ampersand and a double quotation mark after the closing parenthesis. Type any string of text and close with another quotation mark. In the first example above, the formula "=COUNTIF(A1:D12,"Yes")&" Yes"" (without external quotation marks) produces the output "23 Yes."
  • If your target word appears alongside other words or characters within a cell, add asterisks on both sides of the word in the formula. For example, "=COUNTIF(A1:D12,"*Yes*")" counts a cell as containing "Yes" even if the cell reads "Yes 2" or "@Yes."
  • The formula using SUBSTITUTE and LEN works by simulating the erasure of your target word in each cell with SUBSTITUTE and then counting the difference in the simulation's total character length with LEN.