Corporate datasets are usually lengthy, and it becomes tedious to maintain and sort out data when necessary. MS Excel has some time-saving tools like SUM, LEN, COUNTIF, etc. functions. This article will demonstrate several methods using suitable excel functions to count specific characters in a column in Excel.

**DOWNLOAD PRACTICE WORKBOOK**

You can download the practice book from the link below.

**4 Methods to Count Specific Characters in Column in Excel**

We will use the following sample dataset throughout the article to describe the 4 methods to count specific characters in a column using **SUM**, **SUMPRODUCT**, **COUNTIF**, and **LEN **functions.

The dataset contains a list of some books in a library. The librarian wants to find out specific book names containing specific character(s) and count them up. Let’s find out how he can do it using Excel functions.

**1. Use of the SUMPRODUCT Function to Count Specific Characters in a Column in Excel**

**SUMPRODUCT **is a function that can be nested 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.

**a. Function of SUMPRODUCT with LEN and nested LEN and SUBSTITUTE**

We can nest the** LEN** with **SUBSTITUTE** and subtract it with the** LEN** where both are nested in the** SUMPRODUCT** to form a formula to get the count of specific characters from a column.

Let’s consider that the librarian wants to find the count of “**s”** characters from the list of book names.

The range here is **B5:B9 **and the specific character we want to count is **“s”**.

We will apply the following formula:

`=SUMPRODUCT(LEN(B5:B9)-LEN(SUBSTITUTE (B5:B9, "s", "")))`

Finally, we can see that the character “**s**” is found **5 times** in the range **B5:B9**.

**Explanation of the Nested Function Formula:**

The formula we’ve used here can be written in a general form:

`=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, "text", "new_text")`

Here the **range **means the **column range** where you want to** count specific characters** from, and the **text **means the character you want to count.

The **new-text** part is kept empty as we just want to count the specified characters, not substitute them.

`=SUMPRODUCT(LEN(B5:B9)-LEN(SUBSTITUTE(B5:B9, "s", "")))`

**b. Formula of SUMPRODUCT nested with EXACT or ISNUMBER and FIND**

Again, we can count specific characters from a column using the **EXACT **function for an exact match of the specific characters or the **ISNUMBER** and the** FIND **functions for a partial match, nested inside the **SUMPRODUCT **function.

Let’s divide this method into two types based on the exact or partial matches.

I. If the librarian wants to find the exact match of the characters from the list let’s say the specific characters are “**The Jungle List: A Novel**”. For counting for the exact match of these characters we can apply the formula written below:

`=SUMPRODUCT(--EXACT(C5, B5:B9))`

The list contains the characters **1 time** only in the range **B5:B9**. Hence the result is **1**.

**Explanation of the Nested Function Formula for this Method:**

Here, the syntax of the formula is:

`=SUMPRODUCT(--EXACT("text", range)`

Here, for the forced conversion of Boolean true/false to the numerical value, we have used the **“–” double minus** sign. This helps to do further calculations. The **EXACT **function contains the text which indicates the specific characters that need to be matched. It also contains the **range** where the characters are to be matched.

*Note: In the case of using the EXACT function, the match must be exact to get the result otherwise it won’t show the result for some or a single character. This can be shown below:*

*You can notice using “The” as the characters to be matched gives a result of zero (0) since it cannot find the exact match with only “The” characters.*

`=SUMPRODUCT(--EXACT(C5, B5:B9))`

II. To avoid such sensitive cases, we’ll use partial matches. From the given dataset we will try to count for “S” this time.

The formula for the partial match with the specific character will be:

`=SUMPRODUCT(--ISNUMBER(FIND(C5, B5:B9))`

**Explanation of the Above-Mentioned Formula:**

The syntax of the formula:

`=SUMPRODUCT(--ISNUMBER (FIND (“text”, range)`

Here, the “–” double minus sign is also doing the same conversion of data types and other arguments are also similar. The function used for partial matches is different from the exact one. We’ve used the **ISNUMBER** and **FIND** functions here to match the text partially.

*Note: For both the 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.*

`=SUMPRODUCT (--ISNUMBER (FIND (C5, B5:B9))`

**Read More: How to Count Number of Characters in a Cell in Excel (Easiest 6 Ways)**

**2. Count Special Characters in a Column in Excel Using the COUNTIF Function**

Moreover, to **count the number of times the specific character** is found in the column, you can use the simple** COUNTIF** function. Let’s say the librarian wants to find the character **“B”** from the column. In this case, the formula will be:

Now, there are two ways for this based on finding the character at the beginning or anywhere in the column.

I. Let’s say we want to count for the book names which **start with “B”**. For this, we need to add **“*” at the end **of the text in the formula. The formula we need to apply for this is:

`=COUNTIF (B5:B9,” B*”)`

Since only one book name **starts with “B”**, the result is** 1**.

`=COUNTIF (B5:B9,” B*”)`

II. However, you might want to find several books where the character** “B” is anywhere** in the book name. You must put **“*” at both the start and end of “B”** to get the desired result.

`=COUNTIF(B5:B9),"*B*")`

You can see that the dataset contains two book names that have **“B”** at the start of a string **anywhere **in the name. Thus, the result is **2** in this case.

**Explanation of COUNTIF Formula for this Method:**

The syntax of the formula:

`=COUNTIF (range, “text”)`

Here, the function **COUNTIF** takes simple arguments. These are the range of the column and specific character text.** “*”** sign is used either on both sides or at the beginning of the character to get the result for data having the character anywhere inside or at the beginning.

`=COUNTIF (B5:B9,” *B*”)`

**Read More:** **Excel Count Specific Characters in Cell (4 Quick Ways)**

**3. Use of the LEN Function to Count Specific Characters in a Column**

Subtracting **LEN** from nested **SUBSTITUTE** with **LEN **can also give the result for counting specific characters in the data of each column. Following is the formula to show how many times “s” is present in the column:

`=LEN (B5:B9)-LEN (SUBSTITUTE (B5:B9, “s”, “”)`

The result will show values for each data in the column.

**Explanation of Nested function Formula for this Method:**

The syntax of the nested formula:

`=LEN (range)-LEN (SUBSTITUTE (range, “text”, “”))`

Here, the **LEN** function contains the range of the column. The second **LEN** function takes the **SUBSTITUTE** function nested with it which contains the range of the column and specific character. The value from this formula is subtracted from the value of the first LEN function to find the count of specific words found in each row of the column.

*Note: The formula is case sensitive and so columns having no small letter “s” show zero (0) as result.*

`=LEN (B5:B9)-LEN (SUBSTITUTE (B5:B9, “s”, “”)`

**4. Use the SUM Function to Count Definite Characters in a Column in Excel**

For the purpose of counting specific characters in a column, we can also use the **SUM** function. The formula for finding the count of, say, “T” is:

`=SUM (LEFT (B5:B9,1) =” T”) *1)`

Since there are two columns having “T” at the left, the result is 2.

**Explanation of Nested function Formula for this Method:**

The syntax of this formula:

`=SUM (LEFT (range, [num_chars]), =”text”) *1)`

Here, the **LEFT** function is to look up the leftmost character in the column. “**num_char**” indicates the number of characters it will look up for. The **text **contains the given specific character. Afterward, multiplying the value by **1** is to convert the Boolean value to numerical one that is it will give **1*1=1 for Boolean 1** and **1*0=0 for Boolean 0**. The result is passed through the **SUM** function to get the count result.

`=SUM (LEFT (B5:B9,1) =” T”) *1)`

## Things to Remember

Evidently, most of the formulas are case-sensitive. Therefore, be careful while applying the methods explained above. If you are not getting the desired result, check for the case of the character.

## Conclusion

The article explains four different methods of using Excel formulas to count specific characters in a column in Excel. The formulas include nested versions of **SUMPRODUCT, LEN, SUBSTITUTE, and SUM, LEFT**. Besides, it also includes **COUNTIF**. You can use any of the ways which are suitable to your requirements.

Amazing! Thanks so much for this formula! The 1a did the trick. Thanks again 🙂