How to Count Specific Words in Excel

To count specific words in Excel, you can use a combination of functions, including SUMPRODUCT, LEN, and SUBSTITUTE. Follow these steps:

1. Open your Excel workbook and make sure your data is organized properly.
2. Click on an empty cell where you want the result to be displayed.
3. Type the following formula, replacing "A1:A10" with the range of cells containing the text and "word" with the specific word you want to count:

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"word","")))/LEN("word"))

4. Press Enter to apply the formula, and Excel will display the count of the specific word in the selected range.

Example

Let's consider the following data in Excel:

A
1  The quick brown fox
2  jumps over the lazy dog
3  The quick brown cat
4  jumps over the lazy dog

Suppose we want to count the number of occurrences of the word "jumps" in the range A1:A4.

1. Click on an empty cell (e.g., B1).
2. Type the following formula:

=SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"jumps","")))/LEN("jumps"))

3. Press Enter to apply the formula.
4. Excel will display "2" in cell B1, indicating that the word "jumps" appears twice in the selected range.