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.
Count specific characters in a range
Here’s how a general formula looks using several common Excel functions:
…..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.
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:
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.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.