You can use the following formulas to count the occurrence of specific words in Excel:
Method 1: Count Occurrence of Specific Word in Cell
=(LEN(A2)-LEN(SUBSTITUTE(A2,"word","")))/LEN("word")
This particular formula counts how many times “word” occurs in cell A2.
Method 2: Count Occurrence of Specific Word in Range
=SUMPRODUCT((LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"word","")))/LEN("word"))
This particular formula counts how many times “word” occurs in the cell range A2:A8.
The following examples show how to use each formula in practice with the following column of text in Excel:
Example 1: Count Occurrence of Specific Word in Cell
We can type the following formula into cell B2 to count how many times the word “Three” occurs in cell A2:
=(LEN(A2)-LEN(SUBSTITUTE(A2,"Three","")))/LEN("Three")
We can then drag and fill this formula down to each remaining cell in column B:
Column B shows how many times the word “Three” appeared in the corresponding cell in column A.
Note: This formula is case-sensitive. For example, the word “three” will not be counted.
Example 2: Count Occurrence of Specific Word in Range
We can type the following formula into cell B10 to count how many times the word “Three” occurs in the cell range A2:A8:
=SUMPRODUCT((LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"Three","")))/LEN("Three"))
The following screenshot shows how to use this formula in practice:
We can see that the word “Three” occurs a total of 6 times in the cell range A2:A8.
To create a case-insensitive formula, we can use the UPPER function in Excel as follows:
=SUMPRODUCT((LEN(A2:A8)-LEN(SUBSTITUTE(UPPER(A2:A8),UPPER("Three"),"")))/LEN("Three"))
We can type this formula into cell B10 to count how many times “Three” (regardless of case) occurs in the cell range A2:A8.
We can see that the word “Three” (regardless of case) occurs a total of 8 times in the cell range A2:A8.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count If Cells Contain Text
Excel: How to Use COUNTIF with Multiple Ranges
Excel: How to Count Unique Values Based on Multiple Criteria