Rifat Hassan
While working in Microsoft Excel, we often have to count cells with some specific text. Today I will show you how to count cells with specific text in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
5 Easy Ways to Count Cells with Specific Text in Excel
In this article, you will learn how to count cells with specific text in Excel by using the COUNTIF function, combining the SUMPRODUCT function and the EXACT function, and combining the SUMPRODUCT function, the ISNUMBER function, and the FIND function. Let us look at the data set. We have records of various books from a bookstore named Kingfisher Bookstore.
1. Using COUNTIF Function to Count Complete Cell in Excel
We want to find out how many biographical novels there are. We have to match the complete cells of the column Book Type.
COUNTIF() Function
- It takes two arguments, the range of cells and one specific criterion.
- Gives the number of cells that match the specific criterion within that range of cells as output.
Step 1:
- Firstly, choose the C18 cell.
- Secondly, type the following formula below here.
=COUNTIF(E5:E16,"Biographical Novel")- Then, press ENTER.
Step 2:
- Finally, the given image displays the number of Biological Novels and the value is 5.
Read More: How to Count Number of Cells with Dates in Excel (6 Ways)
2. Utilizing COUNTIF Function to Count Partial Cells with Specific Text in Excel
Here, we will determine the number of cells with specific text for partial cells at any of the positions. Here is our data set where we will apply the COUNTIF function to determine the number of cells with specific text for different positions.
2.1.Partial Cell at Beginning
Here, we want to find out all the Book Types starting with “historical”.
Step 1:
- Firstly, choose the C18 cell.
- Then, write down the following formula below here.
=COUNTIF(E5:E16,"Historical*")- Then, hit ENTER.
Step 2:
- Lastly, the given image displays the number of book types starting with Historical and There are 3 Book Types starting with the text “Historical”.
2.2.Partial Cell at End
Now, we want to find all the Book Types ending with “Novel“.
Step 1:
- Firstly, choose the C18 cell.
- Then, write down the following formula below here.
=COUNTIF(E5:E16,"*Novel")- Then, hit ENTER.
Step 2:
- Lastly, the provided picture shows how many different book categories end in “Novel.” So, there are in total 11 novels.
2.3.Partial Cell in Middle
In this section, we want to find all the Book Types with “cal” in the middle.
Step 1:
- Firstly, choose the C18 cell.
- Then, write down the following formula below here.
=COUNTIF(E5:E16,"*cal*")- Then, hit ENTER.
Step 2:
- As a result, you will see that there are 9 Book Types with “cal” in the middle.
Limitations of COUNTIF() Function
- COUNTIF() function can not count correctly if the specific text contains more than or close to 255 characters.
- It raises a #Value Error if you take a range of cells from another workbook as its argument, and the workbook is closed.
Read More: How to Count If Cell Contains Number (Easiest 7 Ways)
Similar Readings
- Count Empty Cells in Excel (4 Ways)
- How to Count Cells That are Not Blank in Excel (8 Useful Methods)
- Excel Count Cells with Numbers (5 Simple Ways)
- How to Count Filled Cells in Excel (5 Quick Ways)
3. Combining SUMPRODUCT and EXACT Functions to Count Complete Cell
In this part, we will demonstrate to you how to count complete cells with specific text in Excel by combining the SUMPRODUCT function and the EXACT function.
SUMPRODUCT() Function
- Takes a range of numbers or cells as input.
- Gives their mathematical sum as output.
Exact() Function
- Takes two inputs, a specific text and a range of cells.
- Returns Boolean values, True if the text matches completely with the cell, and False if it does not match.
Step 1:
- Firstly, choose the C18 cell.
- After that, type the following formula below here.
=SUMPRODUCT(--EXACT("Leo Tolstoy",C5:C16))- Then, press ENTER.
Formula Breakdown
- EXACT(“Leo Tolstoy”,C4:C15): This function act as an argument in the SUMPRODUCT function which returns a sequence of Boolean values, TRUE and
- “–”: This symbol converts the Boolean values into 1 and 0. 1 for TRUE and 0 for FALSE.
- SUMPRODUCT(–EXACT(“Leo Tolstoy”,C4:C15)): This function returns the sum of the 1’s and 0’s. This is the number of times Leo Tolstoy is exactly belonging to the Authors’ list.
Step 2:
- So, we find there are 3 books written by Leo Tolstoy.
Read More: Excel Formula to Count Cells with Text (All Criteria Included)
4. Combining SUMPRODUCT, ISNUMBER, and FIND Functions to Count Partial Cell
In this section, we shall find out how many books have been written by the Bronte sisters. That means either by Emily Bronte or by Charlotte Bronte. We will just match the text “Bronte” partially with column C.
FIND() Function
- It takes two inputs. One specific text and a range of cells.
- Returns the position of the text in a cell if it matches partially with any cell (case sensitive) and returns an error if it does not match.
ISNUMBER() Function
- Takes the output returned by the FIND() function as input.
- Converts the numbers as TRUE and errors as FALSE.
Step 1:
- Firstly, choose the C18 cell.
- After that, type the following formula below here.
=SUMPRODUCT(--ISNUMBER(FIND("Bronte",C5:C16)))- Then, hit ENTER.
Formula Breakdown
- FIND(“Bronte”,C5:C16): This function returns the position of the text “Bronte” in the cells of column C, if it finds any, otherwise returns an error.
- ISNUMBER(FIND(“Bronte”,C5:C16)): This function converts the numbers into TRUE and the errors into FALSE.
- The “–” sign converts the TRUE and FALSE into 1 and 0.
- SUMPRODUCT(–ISNUMBER(FIND(“Bronte”,C5:C16))): The function gives the sum of all the 0’s and 1’s. This is the number of times the word “Bronte” is found in the Authors’ list.
Step 2:
- So, we find the total number of books available for the Bronte sisters is 4.
Read More: Excel Count Number of Cells in Range (6 Easy Ways)
5. Using COUNTIF to Count Specific Text for Multiple Criteria in Excel
Now we go to something a bit more complex. We want to find out the total number of books written by Leo Tolstoy but published after the year 1870.
We will use Excel’s COUNTIFS() function here.
COUNTIFS() Function
- Takes more than one range of cells and criteria as input.
- Returns the number of times when all the criteria are fulfilled.
Step 1:
- Firstly, select the C18 cell.
- After that, write down the following formula below here.
=COUNTIFS(C5:C16,"Leo Tolstoy",D5:D16,">1870")- Then, press ENTER.
Step 2:
- Here COUNTIFS() takes two ranges of cells and two criteria as input.
- It finds “Leo Tolstoy” between cells C5 to C16 and finds years greater than 1870 from cells D5 to D16. Then returns the common number as output.
- Finally, we see the number of books written by Leo Tolstoy published after 1870 is 1.
Read More: How to Count Filtered Rows with Criteria in Excel (5 Easy Ways)
Conclusion
In this article, we’ve covered 5 ways to count cells with specific text in Excel. I sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- How to Count Only Visible Cells in Excel (5 Tricks)
- Count Filled Cells in Excel Using VBA (7 Methods)
- How to Count Non Blank Cells with Condition in Excel (6 Methods)
- Count Odd and Even Numbers in Excel (3 Easy Ways)
- How to Count Blank Cells in Excel with Condition (3 Methods)
- Count Cells with Specific Value in Excel (5 Easy Methods)
