Stagnant cell for counting filtered data

[Lyn]

I want to keep one cell that houses the count of the data depending on how it
is filtered and that cell remain visible despite how it is filtered. I can't
get the formula =COUNT(C9,C1:C100) to work or get it to stay visible if I
change the filter.

 

[Gord Dibben]

Place the formula in row 1 that contains your titles...........assuming you
have these.

=SUBTOTAL(2,C2:C100) the 2, means count

If you don't have titles in row 1, place the formula after a blank row below
your data.

Note: you use COUNT in your original. COUNT only counts numerics.

Maybe you want COUNTA which would be =SUBTOTAL(3,C2:C100)

For more options on subtotal function see help.


Gord Dibben MS Excel MVP