# How to Count Specific Characters in a Column in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful tool for organizing, analyzing, and manipulating data. One useful feature of Excel is the ability to count specific characters in a column of data. In this article, I will explain 4 suitable ways to count specific characters in a column in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.

## How to Count Specific Characters in a Column in Excel: 4 Suitable Ways

In order to count specific characters in a column, Excel has some time-saving tools like SUM, LEN, COUNTIF, SUMPRODUCT, etc. functions. This article will demonstrate several methods using suitable functions to count specific characters in a column. To explain the methods, I have arranged a dataset of book names along with the author names. Let’s dive into detail. ### 1. Use SUMPRODUCT Function to Count Specific Characters in Column

SUMPRODUCT is a function that we can nest with other functions like the LEN, SUBSTITUTE, EXACT, ISNUMBER, and FIND functions. Such nested functions can be used to find out the count of specific characters in a column. Let’s explore the functions one by one.

#### 1.1 Combine SUMPRODUCT, LEN, and SUBSTITUTE Functions

With the help of nested LEN and SUBSTITUTE along with the SUMPRODUCT function, we can combine a formula to count specific characters in a column. The whole process is described below in detail.

Steps:

• Select a cell according to your preference (i.e. D11).
• Next, insert the following formula in that cell and press ENTER to count the number of v in range C5:C9 within column C.
`=SUMPRODUCT(LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, "v","")))` Formula Breakdown

SUBSTITUTE(C5:C9, “v”,””) —> vanishes v from the range C5:C9.
Output: {“Alice in Wonderland”;”Adentures of Tom Sawyer”;”Pride and Prejudice”;”Gullier’s Traels”;”Adentures of Sherlock Holmes”}

LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””))
{19;24;19;18;29}-{19;23;19;16;28} —> subtracts the actual length of cells C5:C9 with the updated length of cells C5:C9 after replacing v.
Output: {0;1;0;2;1}

SUMPRODUCT(LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””)))
SUMPRODUCT({0;1;0;2;1}) —> sums up the values and returns the result.
Output: 4

#### 1.2 Merge SUMPRODUCT and EXACT Functions

We can also merge SUMPRODUCT and EXACT functions to count specific characters in a column. Let’s follow the instructions below to learn!

Steps:

• Pick a cell (i.e. D11) and apply the following formula in that cell to count the number of books with an exact match in the range C5:C9 within column C.
`=SUMPRODUCT(--EXACT("Pride and Prejudice",C5:C9))` Formula Breakdown

–EXACT(“Pride and Prejudice”,C5:C9) —> finds the exact match in the range C5:C9.
Output: {0;0;1;0;0}

SUMPRODUCT(–EXACT(“Pride and Prejudice”,C5:C9))
SUMPRODUCT({0;0;1;0;0}) —> sums up the values and returns the result.
Output: 1

#### 1.3 Combine SUMPRODUCT, ISNUMBER, and FIND Functions

A combination of SUMPRODUCT, ISNUMBER, and FIND functions can also be applicable to count specific characters.

Steps:

• Just select a cell (i.e. D11) and input the following formula in that cell to count the number T in the range C5:C9.
`=SUMPRODUCT(--ISNUMBER(FIND("T",C5:C9)))` Formula Breakdown

FIND(“T”,C5:C9) —> finds T in the range C5:C9.
Output: {#VALUE!;15;#VALUE!;12;#VALUE!}

–ISNUMBER(FIND(“T”,C5:C9))
–ISNUMBER({#VALUE!;15;#VALUE!;12;#VALUE!}) —> returns the number of matched value.
Output: {0;1;0;1;0}

SUMPRODUCT(–ISNUMBER(FIND(“T”,C5:C9)))
SUMPRODUCT({0;1;0;1;0}) —> sums up the values and returns the result.
Output: 2

### 2. Apply COUNTIF Function to Count Specific Characters in Column

To count the number of times the specific character is found in the column, you can use the simple COUNTIF function. Let’s dive into detail.

Steps:

• Apply the following formula in your preferred cell (i.e. D11) to count the number of books starting with V in the range C5:C9 within column C.
`=COUNTIF(C5:C9,"V*")` • To find books having V in their names, insert the following formula.
`=COUNTIF(C5:C9,"*V*")` ### 3. Combine LEN and SUBSTITUTE Functions to Count Specific Characters in Column

Subtracting LEN from nested SUBSTITUTE with LEN can also give the result for counting specific characters in the data of each column. See the section below for more details.

Steps:

• Just input the following formula in your required cell (i.e. D11) to count the number of defined characters in every cell of column C.
`=LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9,"i",""))` Formula Breakdown

SUBSTITUTE(C5:C9,”i”,””) —> vanishes i from the range C5:C9.
Output: {“Alce n Wonderland”;”Adventures of Tom Sawyer”;”Prde and Prejudce”;”Gullver’s Travels”;”Adventures of Sherlock Holmes”}

LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””))
{19;24;19;18;29}-{17;24;17;17;29} —> subtracts the actual length of cells C5:C9 with the updated length of cells C5:C9 after replacing i.
Output: {2;0;2;1;0}

• Press the ENTER button to have the results. ### 4. Merge SUM and LEFT Functions to Count Specific Characters in Column

A merging of SUM and LEFT functions can also be applicable to count specific characters. Let’s go through the following section for more details.

Steps:

• Apply the following formula in your preferred cell (i.e. D11) to count the number of books starting with A in the range C5:C9 within column C.
`=SUM((LEFT(C5:C9,1)="A")*1)` Formula Breakdown

(LEFT(C5:C9,1)=”A”)*1 —> returns the output for books starting with A from the range C5:C9.
Output: {1;1;0;0;1}

SUM((LEFT(C5:C9,1)=”A”)*1)
SUM({1;1;0;0;1})—> sums up the values and returns the result.
Output: 3

Notes
For both cases, the ‘’ double minus sign indicates that the value it receives will be forced to convert the Boolean (True/False) value to either ‘1’ or ‘0’ to do further maths.

## Conclusion

At the end of this article, I like to add that I have tried to explain 4 suitable ways to count specific characters in a column in Excel. It will be a great pleasure for me if this article helps any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

1 Comment
1. Reply Amazing! Thanks so much for this formula! The 1a did the trick. Thanks again 🙂 Advanced Excel Exercises with Solutions PDF  