Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilter COUNTA formula not taking into account hidden cells
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilter COUNTA formula not taking into account hidden cells
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. Sarah wrote: 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, -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTA ----- FORMULA WILL NOT COUNT NON BLANK CELLS | Excel Discussion (Misc queries) | |||
Taking in account additional rows | Excel Discussion (Misc queries) | |||
How to calculate a person's age taking into account their birthda. | Excel Worksheet Functions | |||
repeat formulae taking into account previous column's info? | Excel Discussion (Misc queries) | |||
Date difference taking into account weekends. | Excel Worksheet Functions |