Take a look at =subtotal() in help. It ignores rows hidden by autofilter.

And Aladin Akyurek recently posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFilter'ed range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number of Rome's
that appear in B2:B99 after you filter on some other column (mixture of Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace
Vrange with B2:B99 in that formula.


DD in Virginia wrote:

How do I get formulas to recalculate based on only the visible data after
using the AutoFilter function in Excel 2000? I'm using the COUNTIF and
COUNTA functions to determine a percentage using two separate cell ranges and
I want to be able to filter the data in those ranges and have the formula
recalculate based on the filtered data.

--

Dave Peterson