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. LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””)) SUMPRODUCT(LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””)))
Output: {“Alice in Wonderland”;”Adentures of Tom Sawyer”;”Pride and Prejudice”;”Gullier’s Traels”;”Adentures of Sherlock Holmes”}
{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({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. SUMPRODUCT(–EXACT(“Pride and Prejudice”,C5:C9))
Output: {0;0;1;0;0}
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. –ISNUMBER(FIND(“T”,C5:C9)) SUMPRODUCT(–ISNUMBER(FIND(“T”,C5:C9)))
Output: {#VALUE!;15;#VALUE!;12;#VALUE!}
–ISNUMBER({#VALUE!;15;#VALUE!;12;#VALUE!}) —> returns the number of matched value.
Output: {0;1;0;1;0}
SUMPRODUCT({0;1;0;1;0}) —> sums up the values and returns the result.
Output: 2
Read More: How to Count Specific Characters in a Cell in Excel
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*")
Read More: How to Count Characters in Cell Including Spaces in Excel
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. LEN(C5:C9)-LEN(SUBSTITUTE(C5:C9, “v”,””))
Output: {“Alce n Wonderland”;”Adventures of Tom Sawyer”;”Prde and Prejudce”;”Gullver’s Travels”;”Adventures of Sherlock Holmes”}
{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.
Read More: How to Count Characters in Cell without Spaces in Excel
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. SUM((LEFT(C5:C9,1)=”A”)*1)
Output: {1;1;0;0;1}
SUM({1;1;0;0;1})—> sums up the values and returns the result.
Output: 3
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Related Articles
- How to Count Alphabet in Excel Sheet
- Excel VBA: Count Characters in Cell
- How to Count Space Before Text in Excel
- How to Count Occurrences of Character in String in Excel
<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Amazing! Thanks so much for this formula! The 1a did the trick. Thanks again 🙂