Excel: How to Count Specific Characters in Row


You can use the following basic syntax in Excel to count specific characters in a row:

=SUMPRODUCT(LEN(B1:H1)-LEN(SUBSTITUTE(B1:H1,"a","")))

This particular formula counts the number of characters equal to “a” in the row range B1:H1.

Note: To find the count of a different specific character, simply replace “a” with a different character in the formula.

The following example shows how to use this formula in practice.

Example: Count Specific Characters in Row in Excel

Suppose we have the following row in an Excel spreadsheet that contains the names of various basketball teams:

Suppose that we would like to find the total count of the character “a” in all of the team names.

We can type the following formula into cell B2 to do so:

=SUMPRODUCT(LEN(B1:H1)-LEN(SUBSTITUTE(B1:H1,"a","")))

The following screenshot shows how to use this formula in practice:

Excel count specific characters in row

We can see that there are 3 total characters equal to “a” in all of the team names.

We can verify that this is correct by identifying each “a” in each team name:

  • Mavericks
  • Warriors
  • Lakers
  • Clippers
  • Nuggets
  • Spurs
  • Nets

We can see that there are indeed a total of 3 “a” characters in all of the team names.

If we would like to find the count of a different character in the row, we can replace the “a” with something else.

For example, we can use the following formula to count the number of “W” characters in all of the team names:

=SUMPRODUCT(LEN(B1:H1)-LEN(SUBSTITUTE(B1:H1,"W","")))

The following screenshot shows how to use this formula in practice:

We can see that there is only 1 “W” in all of the team names, which occurs in the “Warriors” team name.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Specific Words in Excel
How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *