Autofilter COUNTA formula not taking into account hidden cells

G

Guest

I'm working on a table with Autofilter, quite a simple one, about 10 columns,
32 lines.
one of the columns is called "destination country" and included several
countries for eg. France, US, Germany etc...
At the bottom of the table I have set up a COUNTA formula =COUNTA(F8:F40)
which works fine when the entire table is displayed.
When I select only one country to come up on the autofilter table eg. USA,
the
COUNTA formula will still show the same result as it keeps on included the
hidden and not hidden cells.
Can you please help me finding out a solution so that the COUNTA formula
only shows the number of items that are NOT HIDDEN in the autofilter table
and
does not take into account HIDDEN lines?

Thank you very much,
 
D

Dave Peterson

Look at =subtotal() in excel's help.

=subtotal(3,f8:f40)
or with xl2003+
=subtotal(103,f8:f40)

The second formula will ignore rows that were hidden manually.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top