Count Filtered Records

[Pat Mattimoe]

Sometimes when I filter a list, the status bar displays
how many records are in the filtered list e.g. "70 of 174
records found".

Other times, the status bar only shows "Filter Mode".

Can anyone tell me why this is or how I can get Excel to
always show the number of records filtered.

Thanks and Happy Xmas


Pat Mattimoe

 

[Debra Dalgleish]

When your list has many formulas, the Status Bar may show "Filter Mode"
instead of a count of the visible records. You can use the Subtotal
function to count the visible rows.

The following MSKB article has some information:

XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode"
http://support.microsoft.com/default.aspx?id=213886

And a couple of workarounds:
http://www.contextures.com/xlautofilter02.html#Count

 

[JMay]

When dealing with data that is filtered don't expect the SUM() feature to
perform as it continues to add visible AND Invisible cells in the range; To
get what you want you'll have to move to the Subtotal(Arg,Range) where if
arg = 9, you get the Sum and where arg = 3, you get the Count (in both cases
of just the visible rows)

Sample:
To Count your datarows enter: =Subtotal(3,A2:A1000)
To Sum your numeric datarows enter: =Subtotal(9,B2:B1000)