CountA with filtered column

[Lee Coleman]

How do I count the values in the rows showing in a column after applying a
filter to the worksheet? When I do something like =counta(b2:b450) I get 449
but this is not correct after using the filter.
Thanks,

 

[Don Guillett]

Have a look in the help index for SUBTOTAL

 

[George Gee]

Lee

Try this:
=SUBTOTAL(3,B2:B450)

George Gee

 

[Lee Coleman]

Thanks it worked great.

Lee

Lee

Try this:
=SUBTOTAL(3,B2:B450)

George Gee

 

[Ken Wright]

Note that this works only with filtered rows, and not if you hide any of
them manually. If you do want to hide any manually then this can be catered
for as well by adding 100 to the argument, so where you might use the
argument 3 as per the example given to you, then 103 will allow you to
exclude manually hidden rows as part of your filtered data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

Thanks it worked great.

Lee