How do I count the number of filtered rows

  • Thread starter Thread starter Pat Bell
  • Start date Start date
P

Pat Bell

In Excel 2002 I have applied a filter and want to count the
matching rows. Rows(A1..A700) just says 700 even though there
are only 600 (100 are filtered out), similarly counta(A1..A700)

How do I count filtered rows?
 
Pat,
try,
=SUBTOTAL(3,A1:A700)
above will counta A1 if it has the header
if that is the case I think
=SUBTOTAL(3,A2:A700)
is better and 3 in the above formula is for counta
look in the help to find the relevant function numbers for other functions
like count and sum.
Cecil
 
You could use a countif function. Unfortunately, you would need to
respecify your criteria.
 
In Excel XP (I believe 2000 also) a possible worksheet solution might be:

=SUBTOTAL(3,INDEX(_FilterDatabase,0,1))-1

You will get a "name" error until an AutoFilter has been applied to the
sheet for the first time.
HTH. :>)
 
Back
Top