How to Count Specific Words in Excel (With Examples)

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.