+ Reply to Thread
Results 1 to 8 of 8

problem applying formula to countifs only visible cells after filtering

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    14

    problem applying formula to countifs only visible cells after filtering

    Hello,
    I was using the following COUNTIF formula for each location ID. =COUNTIFS(FreshPack!J3:J500,"Presumptive positive",FreshPack!F3:F500,"3070")
    However, now I want to be able to filter the data by date/month, etc.

    I was looking for a way to do it, and came up with a previous thread, but the formula is not working for me (it gives me a Zero, instead of the actual count).

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(FreshPack!J3:J500,ROW(FreshPack!J3:J500)-MIN(ROW(FreshPack!J3:J500)),0,1)),(FreshPack!J3:J500="Presumptive positive")*(FreshPack!F3:F500="3070"))

    How can I get it to work? Help please!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: problem applying formula to countifs only visible cells after filtering

    Hi ,

    This formula does work :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula is an array formula , to be entered using CTRL SHIFT ENTER.

    You do not have data for ID of 3042.

    Also , the data in column F is numeric ; thus using 3070 within quotes , as in "3070" will not work.

    Narayan
    Last edited by NARAYANK991; 11-08-2019 at 01:58 AM.

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: problem applying formula to countifs only visible cells after filtering

    The below would do what you want
    Note: the MIN isn't required, and thus nor is the Array entry (prior post)

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, the above will be Volatile (c/o OFFSET) so if, in real life, your precedent ranges are either very big and/or you intend to perform a lot of calcs on filtered datasets you will be much better off adding the SUBTOTAL calc to the table, and by much I mean considerably! So, for ex.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    you can then persist with standard, non-volatile, SUMIFS approach using Col L to generate the 'count' - e.g. SUMIFS(L3:L500,....)
    SUMIFS is more efficient than COUNTIFS when applied to big ranges, which is why I would go that route.

  4. #4
    Registered User
    Join Date
    11-07-2019
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    14

    Re: problem applying formula to countifs only visible cells after filtering

    Quote Originally Posted by XLent View Post
    The below would do what you want
    Note: the MIN isn't required, and thus nor is the Array entry (prior post)

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, the above will be Volatile (c/o OFFSET) so if, in real life, your precedent ranges are either very big and/or you intend to perform a lot of calcs on filtered datasets you will be much better off adding the SUBTOTAL calc to the table, and by much I mean considerably! So, for ex.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    you can then persist with standard, non-volatile, SUMIFS approach using Col L to generate the 'count' - e.g. SUMIFS(L3:L500,....)
    SUMIFS is more efficient than COUNTIFS when applied to big ranges, which is why I would go that route.
    Yes, I think you are absolutely right, it is very volatile, it is working for some of my data points, but not for all of them. It is a hit and miss. I will attempt to do it with the assistant column of Subtotal. If I run into problems I will request your assistance if you don't mind.

    Thanks a lot.

  5. #5
    Registered User
    Join Date
    11-07-2019
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    14

    Re: problem applying formula to countifs only visible cells after filtering

    Thank you. The formula did work when I was using my small data table, however, when I use my actual datasheet with more values it becomes very inconsistent as XLent mentioned below, it becomes volatile with more data. I will attempt the assistant column with Subtotals and see how it goes.

  6. #6
    Registered User
    Join Date
    11-07-2019
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    14

    Re: problem applying formula to countifs only visible cells after filtering

    I think I finally got it. I did the column with Subtotal as you suggested, and tried the SUMIFS formula instead. However that did not work, as I kept getting "Zero". So I went back to using COUNTIFS
    =COUNTIFS(FreshPack!J3:J500,"Presumptive Positive",FreshPack!F3:F500,"3068", FreshPack!L3:L500,1) and that did the trick. Now I can filter my data by year or month, etc. and the values change accordingly.

    Thank you so much for your help!

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: problem applying formula to countifs only visible cells after filtering

    OK - that's good -- yes, the use of SUBTOTAL per row will improve the performance of your model.

    FWIW, the SUMIFS equivalent of the above would be:

    =SUMIFS(FreshPack!L3:L500,FreshPack!J3:J500,"Presumptive Positive",FreshPack!F3:F500,"3068")

    On a final aside, for avoidance of doubt, Volatility does not (really) concern consistency of results, but rather the calculation overhead.

    XL is smart enough to limit what it recalculates - i.e. it does not recalculate absolutely everything when it runs a calculation, (unless forced to), however, there is a limit to how clever it is, or can be

    I always dumb the concept of Volatility, for my own benefit, to a basic rule-of-thumb:

    is the precedent range explicit, or implicit ?

    If the precedent range is explicit, e.g. B1: =A1, the calc tree / XL can easily determine whether or not B1 needs to be recalculated based on what triggered the calculation in first instance.
    If A1 changed it will recalculate B1, if something else changed it won't recalculate B1 as it knows it's not impacted.

    If the precedent range is implicit, e.g. B1: =OFFSET(A1,0,0), when a calculation is invoked XL will recalculate B1 as without evaluating the OFFSET it simply doesn't know which cell(s) B1 is actually reliant upon, to know if what triggered the calculation is relevant, so it plays safe, for sake of integrity, and just calculates B1 regardless.

    It is this inability for XL to differentiate, and thus these increased calculations, that causes the overhead / slowdown...and this impact is exacerbated if there are lots of Volatile functions, or calculations dependent on Volatiles, or if used with already 'expensive' calculations like SUMPRODUCT.

  8. #8
    Registered User
    Join Date
    11-07-2019
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    14

    Re: problem applying formula to countifs only visible cells after filtering

    Thank you so much for the explanation. I get it now.

    For some reason, now your first formula without subtotal column is working now hahaha. Oh well, I will keep the Subtotal column and work it like that.

    Have a good one. My problem is solved!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need Help With the best way to go about applying a COUNTIFS formula
    By cmello1314 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-02-2019, 04:01 PM
  2. countifs in visible cell issues (filtering mode)
    By pepelepew in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-22-2016, 06:19 AM
  3. Apply formula only to visible cells after filtering
    By excelnd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-28-2012, 06:47 AM
  4. [SOLVED] Applying code only to visible cells
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2012, 11:07 AM
  5. Replies: 8
    Last Post: 07-14-2012, 10:22 AM
  6. problem with applying the formula to multiple cells
    By mahmoodn in forum Excel General
    Replies: 3
    Last Post: 11-04-2011, 09:01 AM
  7. Applying Formulas to Visible Cells Only
    By SteveC in forum Excel General
    Replies: 7
    Last Post: 06-26-2006, 06:50 PM

Tags for this Thread

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