How to count cells that are filtered in Excel

How to count cells that are filtered in Excel

To separate a list of items or a list of objects in any situation, you need to use distinctive differences to filter them from each other. Filtering in this scenario is the act of separating or putting or keeping aside some objects and leaving the others.

You need to have criteria to use to do good filtering, you can separate items or objects on the basis of their names, origin, material, cost, and may more. Filtering as it is done in different fields; it is also done in Microsoft Excel sheets.

Our main aim is not how to filter but how to count cells that have undergone filtering. Filtering in most cases does the hiding of data that you have chosen to filter. Counting the filtered cells is the act of coming up with a tally of all the cells that have undergone filtering in a certain data set in excel sheets. So if you are to count the filtered cells in any given set of data you need to have a set of steps that you will have to follow.


1. Open the excel sheets that you need to work on; by working on I refer to doing the count. In excel you will be only able to see the unfiltered data alone. An example is the form of an image.

In this particular set of data, some cells have been filtered or hidden.

2. Having that set of data in place, we are going to count and obtain the number of filtered cells in the data set. To do this, we are going to use the SUBTOTAL formula. To come up with an accurate count of the cells, we are going to write the formula in the formula space that is in front of the “fx” function. In our case, we need to find the filtered cells from cell A2 to cell A6, so our formula will look like this; =SUBTOTAL (3, A2: A6), after typing the formula click on enter to do the count.

The formula returns the value of all the filtered cells.