Leading the way in Microsoft Office Development

Step 1 - Using the Status Bar

As soon as you have performed a filter on a table of data, the total number of rows that have matched your condition is displayed in the status bar. Bottom left hand corner.

This however is only displayed for a short period of time. After that the status bar will display the text "Filter mode" to indicate that there is a table of data currently filtered on the worksheet.

The following mesage is displayed in the bottom left corner of the status bar.

Step 2 - Using the SUBTOTAL() worksheet function

The subtotal worksheet function can returns the total numbers of displayed values in a list or database column.

The advantage of the SUBTOTAL() function is that it only displays visible rows.

The first parameter passed to the SUBTOTAL() indicates which function to use.

Passing the number 3 indicates that we are using the COUNTA() function

Passing the number 9 indicates that we are using the SUM() function

Alternatively if your list of data is going to change regularly then an alternative to using the cell reference "D3:D12" is to refer to the whole column.

The formula for this would be: =SUBTOTAL(3,D:D)-1

In this case the function returns the total number of cells in the "whole" column that are visible and not empty.

You must be sure to subtract the number of cells that you do not wish to include.

In this case there is one cell which we do not want to include. D2, which is the heading column.

Note that this formula is not in column D, but in column E. The reason for this is to prevent a circular reference.

Step 3 - Things to Remember

The bottom left corner of the status bar is used to indicate if a filter is currently being applied.