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

Posted on 2012-08-29
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
Question by:Berry Metzger
    LVL 10

    Accepted Solution

    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:

    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:
    2. Fill down the formula from U5 to the cells through U16.
    3. Use the SUBTOTAL function with the "SUM" option over that range:

    Author Closing Comment

    by:Berry Metzger
    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
    LVL 10

    Expert Comment

    Yeah, that was a typo. Sorry about that!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now