Table of Contents
To count the number of filtered cells with text in Excel, follow these steps:
1. Apply the filter to the desired range of cells.
2. Click on the drop-down arrow next to the column header and select “Text Filters”.
3. Choose the desired filter option (e.g. “contains”, “does not contain”, etc.).
4. Input the text you want to filter by.
5. Press “Enter” to apply the filter.
6. The filtered cells will be displayed.
7. To count the number of filtered cells, use the “COUNT” or “COUNTA” function and select the filtered range of cells.
8. This will give you the total number of cells containing text that match your filter criteria.
Using these steps, you can easily count the number of filtered cells with text in Excel for your data analysis needs.
Excel: Count Filtered Cells with Text
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: