Count specific words in a range

To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions. In the example shown, the formula in C11 is: =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2)) Note: The formula on this page counts instances of a word in a range. For example, if a cell contains two instances of a word, it will contribute 2 to the total count. If you just want to count cells that contain a specific word, see this simple formula based on the COUNTIF function.

April 14, 2025 · 1 min · 114 words · Michaela Mathis

Table of Contents

This number is then subtracted from the length of the original text.

The result is the number of characters that were removed by SUBSTITUTE.

Then, the number of characters removed is divided by the length of the substring.

Excel formula: Count cells equal to

These numbers represent the number of occurrences of the substring in each cell.

Ignoring case

SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution.

LEN will also count characters in numbers, but number formatting is not included.

Excel formula: Count cells that contain specific text

SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching.

Numbers, punctuation, and spaces are not affected.

SUMPRODUCT Function

The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.

Excel formula: Count specific characters in text string

Excel formula: Count specific words in a cell

Excel LEN function

Excel SUBSTITUTE function

Excel UPPER function

Excel SUMPRODUCT function