Excel Formulas to Count specific word in a range ~ Easy Tutorial!!

We have already learned how to count cells that contain a specific text. In this article, we will learn how to count how many times a specific word appears in the Excel Range. In other words, we will count how many times a word occurred in an Excel range. Let’s get them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

If you want to count the specific a word (or any substring) that appears inside a range of cells, you can use the below formula.

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,txt,””)))/LEN(txt))

Syntax Explanations:

LEN – In Excel, the LEN function is used to find the length of the text string.
SUBSTITUTE – This function helps to replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE function.
SUMPRODUCT – It helps to multiply the corresponding arrays or ranges and returns the sum of the products. Read more on the SUMPRODUCT function.
Range – It represents the input value.
Division (/) – It is used for dividing values or numbers.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.
Minus Operator (-) – This symbol will help to subtract any two values.

Example:

Refer to the below example image.

First, we will enter the input text strings in Column B.
Now we need to count specific characters in a range of cells and sum the values.
You can refer to the following article to count the specific word in a cell.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key to get the result out.

Conclusion:

From this article, you can get to know the formulas to count the number of times a specific word appears inside a range of cells in Excel. Hope you like it. Please feel free to state your query or feedback for the above article.