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

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

The following tutorials explain how to perform other common tasks in Excel: