22 December, 2009

Count the number of Rows after Auto-Filter in Excel

When you first apply an Auto-Filter in Excel, the Status Bar indicates the number of records in the filter (e.g. 517 of 8614). In many cases when you start performing other manipulations, the number disappears. Using a simple formula, you can calculate the filtered total.

Assume you want to count all rows that are not empty -- the CountA function -- and assume you want to count the rows in column A. The formula would be SUBTOTAL(103,A:A)-1. The syntax is as follows: subtotal(function_number, reference), where function_number is 1 of many possible options as illustrated below. I included the "-1" to remove the count for the column heading row.
function_num
(includes hidden values)
function_num
(ignores hidden values)
function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Note: I used Excel 2003 for this example.

No comments: