# Excel: How to Count Specific Characters in a Column

You can use the following formulas in Excel to count specific characters in a column:

Formula 1: Count Specific Characters in One Cell

`=LEN(A2)-LEN(SUBSTITUTE(A2,"r",""))`

This particular formula counts the number of characters equal to “r” in cell A2.

Formula 2: Count Specific Characters in Entire Range

`=SUMPRODUCT(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"r","")))`

This particular formula counts the number of characters equal to “r” in the entire range A2:A11.

The following examples show how to use each formula in practice with the following list of basketball team names in Excel: Let’s jump in!

## Example 1: Count Specific Characters in One Cell

We can type the following formula into cell B2 to count the number of characters equal to “r” in cell A2:

`=LEN(A2)-LEN(SUBSTITUTE(A2,"r",""))`

We can then click and drag this formula down to each remaining cell in column B: Column B now displays the total number of characters in the corresponding cell in column A that ar equal to “r”.

For example:

• Mavs contains 0 characters equal to “r”
• Spurs contains 1 character equal to “r”
• Rockets contains 0 characters equal to “r”

And so on.

Note: This formula is case-sensitive.

## Example 2: Count Specific Characters in Entire Range

We can type the following formula into cell D1 to count the number of characters equal to “r” in the entire range A2:A11:

`=SUMPRODUCT(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"r","")))`

The following screenshot shows how to use this formula in practice: The formula tells us that there are a total of 7 characters equal to “r” among all cells in the range A2:A11.