Excel COUNTIF to Count Cell That Contains Text from Another Cell

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.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


4 Methods to Use COUNTIF Function to Count Cell That Contains Text from Another Cell in Excel

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.

Dataset

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
=COUNTIF(C5:C15,”*”)

Here, the COUNTIF(C5:C15,”*”) syntax counts for the cell containing text. The Asterisk symbol acts as text here in the argument.

Using Asterisk Sign with COUNTIF Function Including Blank Cells contains text from another cells

Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples


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.
=COUNTIF(C5:C15,”?*”)

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.

Using Asterisk Sing with COUNTIF Function Excluding Blank Cells 

Read More: How to Count If Cells Contains Text from List in Excel


Similar Readings


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.
=COUNTIF(C5:C15,”*”&B18&”*”)

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.

Count Cells Using Cell Reference to countif cells contains text from another cell in Excel

  • Sequentially, move to cell C19 and input the formula.
=COUNTIF(C5:C15,”*”&B19&”*”)

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.

Count Cells Using Cell Reference to countif cells contains text from another cell in Excel

Read More: COUNTIF Excel Example (22 Examples)


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.
=COUNTIF(C5:C15, “Red”)

Here, the COUNTIF function counts for the specific text “Red” from the C5:C15 range.

Utilizing COUNTIF for Specific Text to countif cells contains text from another cells

  • Then, again go to cell C19 and insert the formula.
=COUNTIF(C5:C15, “Blue”)

At this moment, the COUNTIF function counts the specific text “Blue” from the C5:C15 range.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


Similar Readings


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.
=COUNTIF(C5:C15, “R*”)

In this formula, the COUNTIF function counts for the text starting with the letter “R” from the C5:C15 range.

Using COUNTIF to Count Cell That Contains Partial Text from Another Cell in Excel

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: COUNTIF vs COUNTIFS in Excel (4 Examples)


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.
=COUNTIF(C5:C15,”>0″)

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.

Applying COUNTIF to Count Cell That Contains Numbers from Another Cell in Excel

Read More: How to Use COUNTIF Between Two Numbers (4 Methods)


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.

Dataset2

📌 Steps:

  • In the C5 cell insert the below formula.
=IF(OR(COUNTIF(B5,”*”&$E$5:$E$7&”*”)), “Yes”, “No”)

$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.

Utilizing COUNTIF Function to Count Cell That Contains Text from the List

  • Eventually, press ENTER and drag down the Fill Handle tool for other cells.

Fill handle

Finally, you get the result just like the image below.

Read More: VBA COUNTIF Function in Excel (6 Examples)


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


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. Visit our website Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of Excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo