Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Count If a Cell Contains Any Text in Excel (4 Methods)

While analyzing, organizing, or processing data in Excel, you may feel the necessity to count cells based on the data such as text that the cell contains. Knowing how to count cells based on various criteria is one of the most fundamental skills that you are required to deal well with data in Excel. Understanding this importance, we have come up with four different methods that you can use to count if a cell contains any text in Excel with ease.


Download the Practice Workbook

You are recommended to download the Excel workbook and practice along with it.


4 Methods to Count If a Cell Contains Any Text in Excel

We have used a sample monthly chocolate sales report throughout the whole article to demonstrate all 4 methods to count if a cell contains any text in Excel.

count if cell contains any text in Excel dataset

Now, we are going to discuss all 4 methods one by one.


1. Use the COUNTIF Function to Count If a Cell Contains Any Text in Excel

You can use the COUNTIF function to count all the cells that contain any kind of text within them.

🔗 Steps:

❶ Select Cell D17 ▶ to store the count result.

❷ Type 

=COUNTIF(C5:C14, "*")

in the cell.

❸ Press the ENTER button.

count if cell contains any text using countif function in excel

 

␥ Formula Breakdown

General Structure: =COUNTIF(range,”*”)

▶ In the range section, we input C5:C14, which is the range of the column in which we’ve run the COUNTIF function.

▶ The next section followed the range holds the criteria, where we input an asterisk (*), which is a wildcard that matches any number of text characters.


2. Add up If a Cell Contains Any Text in Excel Using the SUMPRODUCT Function

You can use the SUMPRODUCT function instead of the COUNTIF function to count the cells that contain any texts within them.

🔗 Steps:

❶ Select Cell D17 ▶ to store the count result.

❷ Type

=SUMPRODUCT(--ISTEXT(C5:C14))
within the cell.

❸ Press the ENTER button.

sumproduct function in excel to count cell contains any text

␥ Formula Breakdown

General Structure: =SUMPRODUCT(–ISTEXT(range))

▶ In the range section, we input C5:C14, which is the range of the column in which we’ve run the SUMPRODUCT function.


3. Count If a Cell Contains Texts with Specific Criteria Using the COUNTIFS Function

If you want to add more criteria while counting cells that contain text then you can use the COUNTIFS function instead of using the COUNTIF function.

In this section, we are going to count only those cells that are not empty; means we will omit all the cells that hold nothing but empty spaces within.

🔗 Steps:

❶ Select Cell D17 ▶ to store the count result.

❷ Type

=COUNTIFS(C5:C14,"*",C5:C14,"<> ")
 within the cell.

❸ Press the ENTER button.

sum up if cell contains any text using countfs function

␥ Formula Breakdown

General Structure: =COUNTIFS(range,”*”,range,”<> “)

▶ In the range sections, we input C5:C14, which is the range of the column in which we’ve run the COUNTIFS function.

▶ The next section followed by the first range is where we input an asterisk (*). Which is a wildcard that matches any number of text characters.

▶ The last section followed by the second range specifies the second criteria, which leaves all the cells that contain only empty spaces from the total count.

Related Content: COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)


4. Add up If a Cell Contains with Partially Matched Text in Excel

In this section, we are going to count only those cells that contain “Almont” as part of the whole text.

🔗 Steps:

❶ Select Cell D17 ▶ to store the count result.

❷ Type

=COUNTIF(C5:C14,"*"&C17&"*")
within the cell.

❸ Press the ENTER button.

Add up If a Cell Contains with Partially Matched Text in Excel

␥ Formula Breakdown

General Structure: =COUNTIF(range,”*”&data&”*”)

▶ In the range section, we input C5:C14, which is the range of the column in which we ran the COUNTIF function.

▶ The next section followed the range holds the criteria, where we input the cell address C17 in the place of “data” in “*”&data&”*”. Which extracts only those cells containing the word “Almond”.

Read More: How to Count Cells in Excel with Different Text (5 Ways)


Things to Remember

📌 The COUNTIF function counts Normal Space as a text value.

📌 Be careful while inserting the range and criteria for each of the functions.


Conclusion

In this article, we have discussed four methods that you can use to count if a cell contains any text in Excel. We have also attached an Excel file that you can download to practice all the methods included in this article. Leave comments in the comment section below regarding problems that you are confronting in Excel, we will respond to you asap.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo