+ Reply to Thread
Results 1 to 9 of 9

Applying a COUNTIF formula only to visible cells in a filtered list.

  1. #1
    Registered User
    Join Date
    09-01-2009
    Location
    Manchester
    MS-Off Ver
    Excel 2000
    Posts
    2

    Applying a COUNTIF formula only to visible cells in a filtered list.

    The title pretty much sums the query up. I have a table of data with formulas (eg COUNTIF ) applied to each column. When I filter the column, by date range or region, say, I would like the formula to return the result just for the visible (filtered) entries. This is the same concept as the SUBTOTAL formula, which provides this functionality but only on a limited number of functions (SUM, AVG,MAX etc).
    Any help solving this would be much appreciated.
    Thanks
    Sandra

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    SUBTOTAL() has a great many functions:
    Please Login or Register  to view this content.
    I believe the 100-level codes are not available in Excel 2000, but all the others are, including count.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    Look at the SUBTOTAL function

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    Given you already make mention of the SUBTOTAL function I presume you know what you can and can't do with it - if you want to do conditional calculations on visible rows (ie COUNTIF) then you're looking at using SUBTOTAL embedded within SUMPRODUCT (or using helpers) - we'd need more info to provide more assistance - ie what are you doing exactly ? Post a sample file if you feel it will help illustrate the issue(s) at hand.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    If you want to count the "x"s in A2:A100....but only on rows visible after filtering then try this formula

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)

  6. #6
    Registered User
    Join Date
    09-01-2009
    Location
    Manchester
    MS-Off Ver
    Excel 2000
    Posts
    2

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    Quote Originally Posted by daddylonglegs View Post
    If you want to count the "x"s in A2:A100....but only on rows visible after filtering then try this formula

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)
    Hi Daddylonglegs
    I'm new to using the forum and am not sure of the protocol/procedures for posts and replies etc. but I have tried the SUMPRODUCT formula you suggested and it has worked which is Brilliant! I am keen to understand the structure of the formula and what it is essentially doing - it seems to be based around the SUBTOTAL function but I am not sure about the OFFSET part and the A2 ROW..... part and why you need the SUMPRODUCT bit too? If you are able to give me any insights, this will really help with future projects. Many Thanks for your asssistance. Sandra

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    This part

    SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))

    returns a 1 for each row in the range if it's visible after filtering, otherwise a zero.

    SUMPRODUCT is a standard way to count with multiple criteria, in this case your 2 criteria are whether the cell is visible....and whether it contains "x". See an exhaustive explanation of SUMPRODUCT here, one of the examples (example 11) shows a version of this type of formula....

    The SUBTOTAL/OFFSET part is now a relatively well known "idiom", credited to Laurent Longre, I believe. See here for more

  8. #8
    Registered User
    Join Date
    07-14-2012
    Location
    Nigeria
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    Hii Daddylonglegs,

    Thanks for your post...it was really useful. However, refering back to your equation SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),A2:A100="X")+0). This equation works okay as long as the row A2 shows up in the visible filtered range. When a new criteria is selected and A2 is no longer visible, this equation returns an error message. Is there a way of making this equation dynamic?
    Last edited by desadarius; 07-14-2012 at 10:12 AM. Reason: Typo

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Applying a COUNTIF formula only to visible cells in a filtered list.

    this is an old post but...it should work ok but you need 103 not 3 things changed in excel 2003> see post #2
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1