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