We may sometimes need to **count the Excel cells** with text for different purposes. That is not hard to do. But, things get complicated when we try to count the cells with text in a filtered dataset. So, this article will show you the effective solutions to **Count Filtered Cells **with **Text **in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

To practice by yourself, download the following workbook.

## Dataset Introduction

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the **Salesman**, **Product**, and **Net Sales **of a company. In this case, we will apply the **Filter **feature to the products. Here, if we notice in the salesman name column, we can see that there is a blank cell and a number.

## 3 Methods to Count Filtered Cells with Text in Excel

### 1. Excel COUNTIFS Function for Counting Filtered Cells with Text

We know **Excel **provides various **Functions **and we use them for many purposes. Such a kind is the **COUNTIFS** function which counts the total number of cells in a range upon following multiple criteria. In this method, we’ll use this** COUNTIFS** function to count text cells in a filtered table. But, the process is not straightforward. Here, we have to introduce a **Helper Column **and in this column, we’ll create a formula with the **SUBTOTAL** function. The **SUBTOTAL** function performs various tasks depending on the function number in the argument. So, follow the below steps to perform the task.

**STEPS:**

- Firstly, select cell
**E5**. Here, type the formula:

`=SUBTOTAL(103,B7)`

**NOTE:** **103 **is the function number of the **COUNTA** function. It returns **1 **for a valued cell, and **0 **if empty.

- Then, press
**Enter**and use the**AutoFill**tool to fill the series.

Here, **E10 **is **0 **as cell **B10 **is empty. And when we apply the **Filter **feature, the formula will return **0 **for any hidden rows whether it is a blank cell or not.

- After that, select the drop-down symbol beside the header
**Product**and there check the**Cable**box in the list. - Next, press
**OK**. As a result, it’ll filter out the product**AC**and will return the product**Cable**only.

- Now, select cell
**C12**and type the formula:

`=COUNTIFS(B5:B10,"*",E5:E10,1)`

Here, the first criteria range is **B5:B10**, and the criteria **Asterisk** (*****) match the sequence of characters. Hence, this part of the formula counts the text cells only in the mentioned range. Our second criteria range is **E5:E10** and the criterion is **1**.

- Finally, press
**Enter**and you’ll get the accurate count result.

**Read More:** **How to Apply COUNTIF When Cell Contains Specific Text**

### 2. Combine SUMPRODUCT, SUBTOTAL, INDIRECT, ROW & ISTEXT Functions to Count Filtered Cells with Text in Excel

But, if we want to avoid introducing an additional helper column in the dataset like above, we have to create a long formula containing multiple functions. In this method, we’ll use the **SUMPRODUCT**, **SUBTOTAL**, **INDIRECT**, **ROW** & **ISTEXT** functions to create the long formula. Therefore, learn the steps given below to know how to count filtered text cells.

**STEPS:**

- In the beginning, select cell
**C12**.

- Then, type the formula:

`=SUMPRODUCT(SUBTOTAL(103, INDIRECT("B"&ROW(B5:B10))), --(ISTEXT(B5:B10)))`

- Eventually, press
**Enter**and you’ll get your desired count result.

**🔎 ****How Does the Formula Work?**

**ISTEXT(B5:B10)**

Firstly, the **ISTEXT** function checks for text values in each cell in the range **B5:B10**.

**ROW(B5:B10)**

The **ROW** function finds out the respective row numbers of the range **B5:B10**.

**INDIRECT(“B”&ROW(B5:B10))**

Then the **INDIRECT** function returns the individual references of all cells in the stated range.

**SUBTOTAL(103, INDIRECT(“B”&ROW(B5:B10)))**

The **SUBTOTAL** function counts for the respective cell references feed by the **INDIRECT** function and return **1 **for the visible cells, **0 **for the hidden and empty cells.

**SUMPRODUCT(SUBTOTAL(103, INDIRECT(“B”&ROW(B5:B10))), –(ISTEXT(B5:B10)))**

At last, the **SUMPRODUCT** function multiplies the elements in the same position of the arrays produced by the **SUBTOTAL** and **ISTEXT** functions and then sums the final array.

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

### 3. SUMPRODUCT, SUBTOTAL, OFFSET, MIN, ROW & ISTEXT Functions Combination to Count Filtered Text Cells

Moreover, we can create another long formula containing the **SUMPRODUCT**, **SUBTOTAL**, **OFFSET**, **MIN**, **ROW** & **ISTEXT** functions to count the filtered text cells. Hence, follow along with the process given below. Here, we use the same dataset as above.

**STEPS:**

- First, select cell
**C12**.

- After that, type the formula:

`=SUMPRODUCT(SUBTOTAL(103, INDIRECT("B"&ROW(B5:B10))), --(ISTEXT(B5:B10)))`

- And then, press
**Enter**. Consequently, the precise count will appear.

**🔎 ****How Does the Formula Work?**

**ISTEXT(B5:B10)**

The **ISTEXT** function will check for text values in the range** B5:B10**.

**ROW(B5:B10)**

The **ROW** function returns the respective row numbers of the range **B5:B10**.

**MIN(ROW(B5:B10))**

The **MIN** function finds out the smallest row from the range** B5:B10**.

**OFFSET(B5:B10, ROW(B5:B10) – MIN(ROW(B5:B10)),,1)**

Then the **OFFSET** function along with the **MIN** and **ROW** functions feeds individual references of all the cells in the specified range.

**SUBTOTAL(103, OFFSET(B5:B10, ROW(B5:B10) – MIN(ROW(B5:B10)),,1))**

The **SUBTOTAL** function counts for the respective cell references and returns **1 **for the visible cells, **0 **for the hidden and empty cells.

**SUMPRODUCT(SUBTOTAL(103, OFFSET(B5:B10, ROW(B5:B10) – MIN(ROW(B5:B10)),,1)), — (ISTEXT(B5:B10)))**

Lastly, the **SUMPRODUCT** function multiplies the elements in the same position of the arrays produced by the **SUBTOTAL** and **ISTEXT** functions and then computes the sum of the final array.

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

## Conclusion

Henceforth, you will be able to **Count Filtered Cells **with **Text **in **Excel** with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.