The COUNTIF function counts for specific criteria. You may want to count your text dataset. The COUNTIF function helps you out in this situation. Also, the COUNTIF function can count both specific text and reference cell values. If you want to count the cells that contain text, you can go through this article. In this article, we’re going to show you four simple and straightforward methods to apply the COUNTIF function to count cell that contains text from another cell in Excel. So, let’s get started.
Excel COUNTIF to Count Cell That Contains Text from Another Cell
You can use the COUNTIF function to count a cell that contains text from another cell in Excel. For doing this, we have taken a dataset of the “Team” wise “Students” list. In the “Team” column, we have put both texts and numbers. Now, we count the text from another cell.
Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.
1. Using Asterisk Sign with COUNTIF Function Including Blank Cells
The Asterisk sign (*) stands for the text, and the COUNTIF function then counts for the cells. Follow the simple steps to do that.
📌 Steps:
- Firstly, go to cell C17 and write down the below formula
Here, the COUNTIF(C5:C15,”*”) syntax counts for the cell containing text. The Asterisk symbol acts as text here in the argument.
Read More: How to Use COUNTIF Function to Count Text from List in Excel
2. Using Asterisk Sing with COUNTIF Function Excluding Blank Cells
You can also count the cells that have text, but this time we exclude the blank cells from our dataset. To do this, you have to insert one extra argument, like a question mark. Follow the steps to do it.
📌 Steps:
- Initially, insert the below formula in cell C17.
Here, the question mark indicates to exclude the blank cells, and the Asterisk symbol (*) works as a text argument. Then the COUNTIF function counts for the cells having text values excluding blanks.
Read More: [Fixed] COUNTIF Function with Wildcard Not Working in Excel
3. Count Cells Using Cell Reference
You may select the specific cell reference to count the cell that contains text from another cell. You can count the number of cells that have the particular text here. Go through the steps to do it.
📌 Steps:
- First of all, move to cell C18 and insert the formula.
Here,
B18 = Text that you want to search from your dataset.
C5:C15= Entire data range from where we want to count for texts.
The COUNTIF(C5:C15,”*”&B18&”*”) syntax takes the text value that existed in cell B18 and counts for it from the C5:C15 range.
- Sequentially, move to cell C19 and input the formula.
The COUNTIF(C5:C15,”*”&B19&”*”) syntax takes the text value that existed in cell B19 and counts for it from the C5:C15 range.
See the image below for proper visualization.
Read More: Count Text at Start with COUNTIF & LEFT Functions in Excel
4. Utilizing COUNTIF for Specific Text
In some cases, you may count on a specific text. This will let you know about the number of cells that contain the specific text in your dataset. For example, in our case suppose we want to count the teams that have the text “Red” and also count the cells that have the text “Blue”. Follow the steps below to do that.
📌 Steps:
- In the beginning, move to cell C18 and write up the formula.
Here, the COUNTIF function counts for the specific text “Red” from the C5:C15 range.
- Then, again go to cell C19 and insert the formula.
At this moment, the COUNTIF function counts the specific text “Blue” from the C5:C15 range.
Read More: [Fixed!] Excel COUNTIF Function Not Working for String “True”
Using COUNTIF Function to Count Cell That Contains Partial Text from Another Cell in Excel
The COUNTIF function can also count partial text. That means if you want to find out the number of cells that have a specific alphabet in their spelling, you can also count for it. Just follow the below steps.
📌 Steps:
- Firstly, go to cell C17 and enter the formula.
In this formula, the COUNTIF function counts for the text starting with the letter “R” from the C5:C15 range.
Note: If you put the asterisk (*) mark after the partial text, then it counts as the text starting with the alphabet. But if you put the sign before the partial text, then it counts for the text that ends with the inserted argument.
Read More: [Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value
Applying COUNTIF Function to Count Cell That Contains Numbers from Another Cell in Excel
We have discussed all the methods for counting cells that have text. But you can also count on cells having numbers. As there are also numbers in our dataset, we will count them now. For executing the operations, please go through the simple steps.
📌 Steps:
- Firstly, go to cell C17 and inserted the formula.
The COUNTIF(C5:C15,”>0″) syntax counts for numbers greater than zero. That means it doesn’t count the blanks and negative values and then returns the counted result just like the image below.
Utilizing COUNTIF Function to Count Cell That Contains Text from the List
Suppose you have a set of “Items” in your dataset. But you want to know if there is any exact match in your “List of Items”? See our below image for a better observation. We have attached the simple formula to do it.
📌 Steps:
- In the C5 cell insert the below formula.
$E$5:$E$7 = The cells for checking items.
Formula Breakdown:
COUNTIF(B5,”*”&$E$5:$E$7&”*”)→ This function takes the text Apple, Pears, and Cherry as input and checks for a match in cell B5.
OR(COUNTIF(B5,”*”&$E$5:$E$7&”*”))→ The OR function then returns the result of the COUNTIF function into the logical arguments. The B5 cell has Apple on its list. Then, it will return 1 as considered a TRUE value. For the other text, it returns 0 as it doesn’t match the item lists.
IF(OR(COUNTIF(B5,”*”&$E$5:$E$7&”*”)), “Yes”, “No”)→ The IF function then shows the result Yes or No for the logical TRUE and FALSE respectively. If the logical argument from the OR function shows 1, then the IF function, considers it TRUE and returns Yes as output. An alternate result of 0 is shown for Boolean logic.
- Eventually, press ENTER and drag down the Fill Handle tool for other cells.
Finally, you get the result just like the image below.
Read More: How to Use COUNTIF Function In Excel to Count Bold Cells
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
That’s all about today’s session. And these are some easy methods to apply the COUNTIF function to count cell that contains text from another cell in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Thanks for your patience in reading this article.
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
