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

Get FREE Advanced Excel Exercises with Solutions!

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.

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. Using 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 following the range holds the criteria, where we input an asterisk (*), which is a wildcard that matches any number of text characters.


2. Adding 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 text 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. Using the COUNTIFS Function to Count If a Cell Contains Texts with Specific Criteria 

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; which 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 criterion, which leaves all the cells that contain only empty spaces from the total count.

Related Content: How to Count Blank Cells with Condition in Excel


4. Adding up If a Cell Contains 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 Non-Blank Cells with Condition in Excel


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.


Download the Practice Workbook

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


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, and we will respond to you ASAP.


Related Articles

Mrinmoy Roy
Mrinmoy Roy

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.

2 Comments
  1. The SUMPRODUCT(–ISTEXT(range)) worked for me. My range included TEXT and zeros. Thank you for being there!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo