Pivot Table COUNTA SUM of Unhidden cells only

[Sarah]

I'm working on a pivot table, 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 pivot 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 pivot table and
does not take into account HIDDEN lines?

Thank you very much,

 

[Ken Wright]

Are you sure it is a Pivot table, or if it is, are you using Autofilter on
the results?

If you restrict your data to USA using pivot table functionality then the
data meeting the criteria is the only data on the sheet, it is not "hidden".

If you are indeed using Autofilter, then you can use the SUBTOTAL function
to do what you require.

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

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