asked on

Excel formulas (two variations) calculate text or values for visible rows with AutoFilter

Hello Experts:
I have two different formulas that I need to calculate a percentage for visible rows when filtering a list. One formula uses the Countif function for text entries, the other formula uses countif values are >1 in the visible filtered list,
Formulas shold work for Excel 2003 and 2010 versions.
Attached is a spreadsheet with the problem better described
Thank you in advance
Berry
Calculate-Visible-Rows-for-Text-.xlsx

ASKER CERTIFIED SOLUTION

tdlewis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Berry Metzger

ASKER

Thanks for the help. Your solution for cell H18 works correctly,

However, the solution for cell Q18 and neighboring cells to the right requires a fix to your formula which I made to get desired results:

2) ADD: ... then copy this range to as many columns as there are formulas that need this 0 or 1 reference e,g,. cols U, V, W, X in this example

3) by changing your formula solution, =SUBTOTAL(9,Q7:A16)/SUBTOTAL(3,names) to read =SUBTOTAL(9,Q7:Q16)/SUBTOTAL(3,names) and then copying this formula from Q18 across required columns does the job.

It would be nice if future versions of Excel add a COUNTIF function to SUBTOTAL to avoid having to add your step 1) as a work around.
Thanks for your prompt response
Berry

tdlewis

Yeah, that was a typo. Sorry about that!