Count specific characters in a range

If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain words and txt represents the character you need to count. In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,"o","")))

April 14, 2025 · 1 min · 103 words · Dale Peters

Table of Contents

This number is then subtracted from the length of the text with o’s.

SUMPRODUCT then sums the numbers in this list and returns a total for all cells in the range.

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

Excel formula: Count specific characters in text string

Then supply an uppercase character for the text that’s being counted.

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

SUBSTITUTE Function

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

Excel formula: Count specific words in a cell

Numbers, punctuation, and spaces are not affected.

SUMPRODUCT Function

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

Excel formula: Cell contains specific text

Excel formula: Count specific words in a range

Excel LEN function

Excel SUBSTITUTE function

Excel UPPER function

Excel SUMPRODUCT function