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
3
Medium Priority
?
439 Views
Last Modified: 2012-08-30
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
0
Comment
Question by:Berry Metzger
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
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

by:Berry Metzger
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.
Thanks for your prompt response
Berry
0
 
LVL 10

Expert Comment

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question