Count Autofiltered Rows

A companion file is available: Click here to download

Q. When I use Excel's AutoFiltering, the status bar displays the number of qualifying rows. But for no apparent reason, that number often vanishes. How do I keep this number visible while I work?

AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering criteria, Excel shows the record count on the status bar--but this value disappears when the sheet is calculated.

To display a permanent count of the visible rows in an AutoFiltered list, create a formula using Excel's SUBTOTAL function. The first argument for SUBTOTAL specifies the type of operation (an argument of 2 displays a count of the visible cells in a range).

The figure below shows a list in rows 6 through 3006. The formula in cell D3 is:

=SUBTOTAL(2,A6:A3006)

The formula counts the number of visible cells in the range (minus the header row). Apply different filtering criteria, and the formula updates to show the new count. The SUBTOTAL function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the correct result.