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

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.

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.

How to Count Filtered Cells with Text in Excel: 3 Methods

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:

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:

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.

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:

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

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:

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

Download Practice Workbook

To practice by yourself, download the following workbook.

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.

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