Table of Contents
You can use the following formula to count the number of filtered cells in a particular range in Excel that contain text:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A13))), --(ISTEXT(A2:A13)))
This particular formula counts the number of filtered cells with text in the range A2:A13.
The following example shows how to use this formula in practice.
Example: How to Count Filtered Cells with Text in Excel
Suppose we have the following dataset that contains information about total sales made by various employees at some company:
Next, suppose we filter the data to only show the rows where the value in the Sales column is greater than 15:
Now suppose we would like to count the number of filtered cells in the Employee column that contain text values.
Normally we could use the following formula to count the number of cells in a range that contain text values:
=COUNTIF(A2:A13, "*")
But if we use this formula on our filtered data, the formula will incorrectly return the number of cells that had text values in our original dataset before we filtered it:
Instead, we must use the following formula to count the number of filtered cells with text:
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A13))), --(ISTEXT(A2:A13)))
The following screenshot shows how to use this formula in practice:
This formula correctly returns a value of 3.
We can confirm this is correct by manually identifying the three cells with text values in the Employee column: Andy, Jim and Craig.