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

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.

...
Appearances of "Yes" counted using COUNTIF.

Video of the Day

Count with COUNTIF

...
Select the cell range.

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".

...
Enter the search term.

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.

...
Finish and adjust the formula.

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.

...
COUNTIF counting another group of cells.

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

...
The LEN and SUBSTITUTE formula.

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

...
The formula filled in.

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.

...
The formula successfully counting "Yes."

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.

Show Comments