Monday, December 06, 2004

MS Excel:Count AutoFiltered Rows

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 formula in cell which will display visible rows is:


=SUBTOTAL(2,A6:A3000)

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.


No comments: