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
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".
Video of the Day
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.
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.
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
Replace both instances of "
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.