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.


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


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.


<< Go Back to With Text | Count Cells | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

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

  2. Hi there! I use the ctrl: to enter dates in a cell. I’m trying to add all cells in this column that have this (this will be the only entry in the column) but it continues to show 0 as the function value. I’ve tried all of these steps.

    • Dear Kelley Sauer,
      Please use this formula below to count sales having date.
      =COUNTIF(E5:E14, "<>")
      Count cells when date is used
      It will count all sales with date. Using this formula, you will not get zero anymore. Moreover I have also used Ctrl+: to insert date.
      With Regards,
      Joyanta Mitra

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo