< Go Back

Count specific characters in a range

To count the times that a specific character appears in a test, use a formula that combines three functions which are the LEN, SUBSTITUTE, and SUMPRODUCT. Here’s how a general formula looks:

Generic Formula

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

…..where “rng” is the range of the cells of interest while “txt” is the character. SUBSTITUTE executes the first step by removing “txt) from the sentence. The first LEN does is provides a count of all characters in the original text while 2nd LEN gives a count of characters after the removal of ‘txt’.  Finally, the SUMPRODUCT does the summation of all results from the cell.

Example

Here’s an example of how the discussed formula can count a specific character in a range. We have texts in 5 cells let’s see how many times “Y” appears. Check out we did it in this example.

Figure 1. Example 1 of SUMPRODUCT/SUBSTITUTE/ LEN Function

The formula in the highlighted cell (C12)  in the screenshot is:

=SUMPRODUCT(LEN(B6:B10)-LEN(SUBSTITUTE(B6:B10,”y”,"")))

Here’s How this formula works

To count the times that “y” is appearing in the range B6:B10, the SUBSTITUTE removes it from the original text of each cell. LEN then count the length of the text in every cell without “Y” and the result is subtracted from the original text’s length. What we get from this subtraction is a count of characters removed in the first step- that is what we are looking for.

If the subtraction result in a cell is 2, it is an indication that character has appeared twice. The same is done for all cells. SUMPRODUCT will then sums the counts from all cells. In our example, the character “y” is appearing 6 times.  

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar