Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

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

Posted on 2012-08-29
Medium Priority
439 Views
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
Berry
Calculate-Visible-Rows-for-Text-.xlsx
0
Question by:Berry Metzger
• 2

LVL 10

Accepted Solution

tdlewis earned 1500 total points
ID: 38348739
You can use the SUBTOTAL function, but you will have to clean up your data because SUBTOTAL does not have a "COUNTIF" option; it only has a "COUNTA" option and cells such as H8 and H9 containing a space also get counted. You might want to apply conditional formatting to highlight any cell that contains a space.

Here's the alternate formula for cell H18:
=SUBTOTAL(3,H7:H16)/SUBTOTAL(3,names)

Getting the desired result in cell Q18, requires a multiple-step process:
1. Choose an unused cell range (such as column U) and insert this formula in cell U5:
=IF(Q5>1,1,0)
2. Fill down the formula from U5 to the cells through U16.
3. Use the SUBTOTAL function with the "SUM" option over that range:
=SUBTOTAL(9,Q7:A16)/SUBTOTAL(3,names)
0

Author Closing Comment

ID: 38348873
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.
Berry
0

LVL 10

Expert Comment

ID: 38349112
Yeah, that was a typo. Sorry about that!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month10 days, left to enroll