Excel 2010 - use COUNTIF and COUNTIFS with filters

Posted on 2012-08-14
Last Modified: 2012-08-14
I have an Excel 2010 document that looks something like this:

Name              Color             Size              Units
Bob Smith         Red               Large                  5
Tim Jones         Blue              Small                  8
Fred Wallace      Red               Small                  1
Ann Smith         Blue              Large                  8

Open in new window

Then, in the cells below my data, I have a formula that counts the number of people whose Units are less than 6:


This returns 2, as it should. The problem is that when I apply filters to the data, this value doesn't change. For example, if I filter for Column D = "Blue", I want the formula to return 0. If I filter for Column C = "Large", I want the formula to return 1.

I also have the same problem with this COUNTIFS function:


Is there a formula I can use that will perform the same function as COUNTIF / COUNTIFS, but will work with the filters?

Thanks for your help.
Question by:LCMSdev
    LVL 70

    Expert Comment

    COUNT and COUNTIF take no notice of filters - instead use SUBTOTAL(2,D2:D5)

    (2 = Count)
    LVL 50

    Accepted Solution

    .....but SUBTOTAL will count all the numeric values that are visible, the requirement here is to only count visible numbers within a specific range.

    For that try this version for counting < 6 excluding filtered out values


    and for >=6 and < 10


    regards, barry
    LVL 50

    Expert Comment

    by:barry houdini
    ...another way which simplifies the formulas required is to use a helper column, e.g. in column E (or any blank column), use this formula in E2 copied down


    you'll get a 1 on visible rows and a zero on the non-visible ones, then you can just use that column in the COUNTIFS formula, e.g.




    regards, barry

    Author Closing Comment


    That's absolutely perfect, thank you very much!

    Had to go through the logic to figure out why this worked. Gotta say, it strikes as a bit...doofy. (Nothing against you, of course! I'd just have thought there'd be a formula that did this cleanly.) This sort of thing is why I personally prefer VBA to formulas, but I can't be telling clients to run macros on their reports. I greatly appreciate your help!

    -- LMCSdev

    Author Comment

    Just noticed your last post; that's a very clever hack with any number of applications. I'll definitely file that one away for future use. Thanks again!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    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…
    PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now