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

S

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...
 
B

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<>""))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting dates in filtered column 1
Adding a big matrix in Word 2013 5
Lookup (more complicated) 2
Count Displayed Rows of a Filtered Worksheet 2
help on a formula (match and etc..) 1
Count 1
Counting unique cells (with text) in a filtered list 5
Return value in an table with variable columns and rows 2

Top