*Figure 1. of Count Specific Words in a Range.*

In order for us to determine the number of times a specific word/text (or a substring) is occurring within a range of cells, we are going to utilize SUMPRODUCT, SUBSTITUTE and LEN Functions. This tutorial will step through the process.

**Generic Formula**

**=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))**

**How to use the SUMPRODUCT, SUBSTITUTE and LEN Functions in Excel**

In the generic formula syntax stated above, the text range to check is represented by “**rng”, **while** “text” **is representing the specific word being checked for.

We can achieve this by following three simple steps;

- Collect and arrange our data values within our worksheet. Label the columns appropriately. See example illustrated below.

* Figure 2. of Text Data for Checking in Excel.*

- Our purpose here is to determine the number of times that the word “Humpty” is occurring within the text strings in column A of our worksheet.

The formula syntax which we will enter into the formula bar for cell C2 to determine the count is;

**=SUMPRODUCT((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,B2,"")))/LEN(B2))**

* Figure 3. of SUMPRODUCT, SUBSTITUTE and LEN Functions in Excel.*

Excel checks our text string and returns the specified word count in column C of our worksheet.

- Modify and copy the formula syntax into the cells down the column to achieve the desired results.

* Figure 4. of SUMPRODUCT, SUBSTITUTE and LEN Functions in Excel.*

**Note**

- Our rng in the example illustrated above is A2:A5
- Our text is in the example illustrated above is “Humpty”

* Figure 5. of Final Result. *

**Instant Connection to an Expert through our Excelchat Service:**

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

## Leave a Comment