How Do I Count Cells with Text In Excel (8 Quick Tricks)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 8 quick tricks to count cells with text in excel. You can use these methods even in large datasets to find out the data cells that contain text values. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


8 Quick Tricks to Count Cells with Text In Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 2 unique columns which are Products and Sales Quantity. Although we may vary the number of columns later on if that is needed.

how do i count cells with text in excel


1. Using COUNTA Function

The COUNTA function counts all the cells with any type of value. We can use this function to count cells with text in excel. Follow the steps below to do this.

Steps:

  • First, go to cell C10 and insert the following formula:
=COUNTA(B5:C9)-COUNT(B5:C9)

counta function to count cells with text in excel

  • Now, press Enter and this will calculate the total number of text cells inside C10.

Read More: Count If Cell Contains Text in Excel (5 Easy Approaches)


2. Applying COUNTIF Function

The COUNTIF function takes certain criteria to count cells. We can count cells with text in excel using this function by setting proper criteria. Let us see how to do this.

Steps:

  • To begin with, double-click on cell C10 and enter the below formula:
=COUNTIF(B5:C9,"*")

countif function to count cells with text in excel

  • Next, press the Enter key and you should get the number of cells with text data.

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


3. Utilizing ISTEXT Function

As the name suggests, the ISTEXT function in excel checks whether or not a cell contains text and gives feedback with a TRUE or FALSE value. Let us see how to apply this here to count cells.

Steps:

  • To begin this method, double-click on cell C10 and insert the formula below:
=COUNT(IF(ISTEXT(B5:C9),1))

istext function to count cells with text in excel

  • Next, press the Enter key and consequently, this will find the total cells count with text data inside cell C10.

🔎 How Does the Formula Work?

  • ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
  • IF(ISTEXT(B5:C9),1): This returns an array of 1 and FALSE depending on the cell containing text value.

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


4. Counting with SUM Function

We can also use the SUM function in excel in combination with the ISTEXT function to count cells with text.

Steps:

  • To start this method, navigate to cell C10 and type in the following formula:
=SUM(IF(ISTEXT(B5:C9),1))

sum function to count cells with text in excel

  • After that, press the Enter key or click on any blank cell.
  • Immediately, this will give you the text cell count inside cell C10 as 5.

🔎 How Does the Formula Work?

  • ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
  • IF(ISTEXT(B5:C9),1): This returns an array of 1 and FALSE depending on the cell containing text value.

Read More: How to Count Specific Words in a Column in Excel (2 Methods)


5. Counting Text Cells by SUMPRODUCT Function

In this method, we will use the SUMPRODUCT function along with the ISTEXT function to count cells with text in excel.

Steps:

  • As previously, insert the below formula inside cell C10:
=SUMPRODUCT(IF(ISTEXT(B5:C9),1))

sumproduct function to count cells with text in excel

  • Finally, press the Enter key and we should get the result as 5.

🔎 How Does the Formula Work?

  • ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
  • IF(ISTEXT(B5:C9),1): This returns an array of 1 and FALSE depending on the cell containing text value.

6. Utilizing SIGN Function in Excel

The SIGN function in excel tests whether a number is positive or negative. We can use this function similar to the previous method to count cells with text in excel. Below are the detailed steps.

Steps:

  • To begin with, the process, navigate to cell C10 and type in the formula below:
=SUMPRODUCT(SIGN(ISTEXT(B5:C9)))

sign function to count cells with text in excel

  • Then, press Enter and this will count the total number of cells with text data value inside cell C10.

🔎 How Does the Formula Work?

  • ISTEXT(B5:C9): This portion checks each cell in the range and returns TRUE if a cell contains text, FALSE
  • SIGN(ISTEXT(B5:C9)): This portion returns an array of 1 and 0 when the cell is positive or 0.

7. Excluding Cells with Space

In the following dataset, cell B8 has a single space which the previous methods will count as text. For this, we need a different way to count cells with text in excel but excluding any cell with space.

Steps:

  • First, go to cell C10 and insert the following formula:
=COUNTIF(B5:C9,"><")

  • Now, press Enter and this will calculate the total number of text cells inside cell C10.


8. Counting Cells After Filtering

Here, we have filtered the dataset as you can notice that row 9 is not present. Regular formulas will count this row as well. So we will use a different formula to count only filtered cells with text in excel.

Steps:

  • For this, double-click on cell C10 and enter the below formula:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("B"&ROW(B5:C10)))*(ISTEXT(B5:C10)))

  • Next, press the Enter key and you should get the number of cells with text data.

🔎 How Does the Formula Work?

  • ISTEXT(B5:C10): The ISTEXT function checks each cell in the range and returns TRUE if a cell contains text, FALSE otherwise.
  • INDIRECT(“B”&ROW(B5:C10)): INDIRECT function to set the individual references of all cells in the specified range.
  • SUBTOTAL(103, INDIRECT(“B”&ROW(B5:C10)))*(ISTEXT(B5:C10)): This portion gives back an array of 1 and 0 which indicates the existence of text in a cell or otherwise.

Read More: How to Count Filtered Cells with Text in Excel (3 Methods)


How to Count Cells In Excel

If you are familiar with VBA in excel, then you count cells quickly with just a few lines of code. Let us see how we can do that.

Steps:

  • For this method, go to the Developer tab and select Visual Basic.

  • Now, select Insert in the VBA window and click on Module.

  • Next, type in the formula below in the new window:
Sub CountCells()
  Range("C10") = Application.WorksheetFunction.CountA(Range("B5:C9"))
End Sub

  • Then, open the macro from the Developer tab by clicking on Macros.

  • Now, in the Macro window, select the CountCells macro and click Run.

  • As a result, the VBA code will calculate the total number of cells inside cell C10.


How to Count Cells with Numbers in Excel

To count cells with numbers, we will the basic COUNT function in excel.

Steps:

  • To begin with, double-click on cell C10 and enter the below formula:
=COUNT(B5:C9)

  • Then, press the Enter key and this will find the number of cells with a number equal to 5.

Read More: How to Count Cells with Specific Value in Excel (5 Easy Methods)


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to count cells with text in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

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.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo