Microsoft Excel has great filtering capabilities when used for some large sets of data. When you work with a filtered list, you probably want to know number of rows that are there in the list. But you probably will get confused on how to do it if you are familiar with Excel's nature.

Things You Will Need

I once applied a filter to some contents of one of my worksheets in Excel. I wanted to count the number of rows that were visible in the filtered data. I tried to count it using COUNT function, but Excel returned to whole number of rows including the rows that not displayed in the filtered list. After some confusions and browsing through internet, I found out that it was actually normal behavior of Excel. Excel's COUNT function returns all the rows in a range, regardless they are visible or not.

After doing some browsing, I found some methods applicable for this situation. Let me briefly explain to you about the methods as follows:

1. Looking at Excel status bar. When we want to do a count and we don't need to get our count in a cell, we can just use Excel to tell us the count. Just apply a filter, the count will automatically appear in Excel status bar. It will show how many rows are displayed by the filter, just what we need. The problem is when we start editing some information in our worksheet, the displayed count will automatically disappear. And it will appear again just after we reapply the filter.

2. Select all the visible cells in a certain column and look at the status bar again. Now point your cursor to that bar, right-click it and select "Count". It will then display the number of column in that filter.

3. Use the COUNTIF function. To do this, we need to make sure that the criteria specified in that function is the same as the criteria we use in the filter. By this, we know that if we change our filter criteria, we will also need to change the function criteria.

4. Use the SUBTOTAL function. I will give you a real example here. For example our filtered information is in column A10:A200. The SUBTOTAL formula we can use to display the count of rows in the filter is :


The first figure, 2, indicates that we instruct Excel to use the COUNT function to determine the formula result. If we change this figure to 3 then SUBTOTAL will use COUNTA instead. Either way, SUBTOTAL will only count the rows displayed by the filter.

So, now you don't have to be confused on how to count your filtered column. You have 4 simple methods applicable to that situation.

Tips & Warnings

These tips are applicable to Microsoft Excel 97, 2000, 2002, 2003 and 2007.