Formula Recalculation after using AutoFilter

[DD in Virginia]

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]

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]

Dave,

Will SUBTOTAL work with the COUNTIF function? Here's an example of the
formula that I want to recalculate after using AutoFilter........

=COUNTIF(J3:J96,"W")/COUNTA($A3:$A96)

Thanks for your help.

 

[Dave Peterson]

It'll evaluate--but it won't be what you want.

=Countif() doesn't ignore the hidden rows--neither does =counta().

But this might work for you:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(J3:J96,ROW(J3:J96)-MIN(ROW(J3:J96)),,1)),
--(J3:J96="W")) / SUBTOTAL(3,A3:A96)

(all one cell.)

 

[DD in Virginia]

Thanks, that formula worked for me. Appreciate your help and insight.