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.

**Table of Contents**hide

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

### 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)`

- 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,"*")`

- 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))`

- 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))`

- 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))`

- 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)))`

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

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