 Count autofiltered rows |
| Excel's AutoFiltering results in rows in a list that don't meet your filter criteria being hidden. 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 two displays a count of the visible cells in a range). Figure 1 shows a list in rows 6 through 3006. The formula in cell D3 is: =SUBTOTAL(2,A6:A3006). It 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. | Category:Spreadsheets Issue: January 2000 |
These Web pages are produced by Australian PC World © 1999 IDG Communications