How do I count the number of filtered rows

[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?

 

[Cecilkumara Fernando]

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

 

[Random]

You could use a countif function. Unfortunately, you would need to
respecify your criteria.

 

[Dana DeLouis]

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. :>)