Counting, (Sub)-Totaling on a Filtered Worksheet...

[skoalnreds]

I'm building a worksheet to store survey results. Row 1 contains all
the formulas for totaling and subtotaling.

In column J, the values in Rows 3 thru 2500 can be either "Y", "N", or
null (blank). In Row 1, I have the formula:

=COUNTIF(J3:J2500,"Y")/COUNTIF(J3:J2500,">""")

which gives the percentage of "Y" values only (ignoring "N" and nulls)
-- this works great until I filter the survey results on some other
column. When I do, the calculation in J1 does not change to reflect
the filtered data.

How can I get the above formula to be dynamic based on what rows are
visible?

Thank you so much in advance...

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(J3,ROW($J$3:$J$2500)-ROW($J$3),,1)),--($J$3:$J$2500="Y"))/
SUMPRODUCT(SUBTOTAL(3,OFFSET(J3,ROW($J$3:$J$2500)-ROW($J$3),,1)),--($J$3:$J$2500<>""))

 

[skoalnreds]

Thank you so much Bob! Works like a charm, of course!