+ Reply to Thread
Results 1 to 14 of 14

countifs in visible cell issues (filtering mode)

  1. 08-21-2016, 05:16 PM #1
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Unhappy countifs in visible cell issues (filtering mode)

    Hello to everyone,

    i'm new here and although i've searched and read all the threads regarding visible cells with formulas using sumproduct,subtotal,row etc
    i still cannot figure out how to use my formulas (countifs) after enabling filters.

    =COUNTIFS(N9:N10000;">0,5";O9:O10000;">0,5")
    =COUNTIFS(N9:N10000;"<0,5";O9:O10000;">0,5")+COUNTIFS(N9:N10000;">0,5";O9:O10000;"<0,5")+COUNTIFS(N9:N10000;"=0";O9:O10000;"=0")

    Can please someone give me a hand?
    i really appreciate any help

    Cross post my issue also here
    http://www.mrexcel.com/forum/excel-q...ring-mode.html
    Attached Files Attached Files
    Last edited by pepelepew; 08-22-2016 at 04:53 AM.
    Reply With QuoteRegister To Reply
  2. 08-21-2016, 05:26 PM #2
    TheN
    TheN is offline
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: countifs in visible cell issues (filtering mode)

    Filters really screw with formulas.

    If you need to filter your data that is determined by formulas, I suggest copy pasting it all as values only, then applying the filters/sorts. Alternatively, you can make it so your formulas do the filtering for you right away (possibly).

    Mind attaching a sample workbook? This can be done by editing your post, then clicking the go advanced button. From there, scroll down to manage attachments and attach your sample.

    Try showing the sample in one tab, and expected results in another tab, mock it up manually if needed. This has to be a large enough sample size to get your point across, nothing too big. Remember to remove sensitive data if there is any.
    Reply With QuoteRegister To Reply
  3. 08-21-2016, 05:34 PM #3
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    thx a lot TheN for your quick reply. Just edit my initial post and attached my excel file. The formulas i would like to fix are in cells u3 and v3. if i apply a filter the cells remain the same...
    Reply With QuoteRegister To Reply
  4. 08-21-2016, 05:53 PM #4
    TheN
    TheN is offline
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: countifs in visible cell issues (filtering mode)

    Umm, so what am I looking at, mind highlighting the cell(s) where you are having the issues or something, also, what you'd like to accomplish.

    Do that in a second sheet, so I can see the before and after. Manually mock up the results if needed.
    Reply With QuoteRegister To Reply
  5. 08-21-2016, 05:58 PM #5
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    fixed.
    in the sheet1 is the initial data and in sheet2 is the data after filtering. i've highlighted the cells with the formulas
    Reply With QuoteRegister To Reply
  6. 08-22-2016, 03:35 AM #6
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    any other ideas would be highly appreciated
    Reply With QuoteRegister To Reply
  7. 08-22-2016, 04:35 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: countifs in visible cell issues (filtering mode)

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    An answer has been provided here: http://www.mrexcel.com/forum/excel-q...ml#post4612057
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  8. 08-22-2016, 04:48 AM #8
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    Dear AliGw

    It was not my intention to cause any inconvenience.
    And certainly i didn't know that there was a cross-post issue (who does really read all the rules :P)
    I just asked for other opinions as i can't wait to end the last formulas.
    However, the solution provided did not worked.
    If it did, be sure i would had already posted here for reference.
    Reply With QuoteRegister To Reply
  9. 08-22-2016, 04:54 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: countifs in visible cell issues (filtering mode)

    Here are the forum rules, to which you agreed upon joining the forum: http://www.excelforum.com/forum-rule...rum-rules.html As you haven't read them yet, now might be a good time to.

    You should also provide a link back to this thread on the other forum where you have posted. As long as you provide these links, everybody will be happy.
    Reply With QuoteRegister To Reply
  10. 08-22-2016, 04:56 AM #10
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    Roger!
    Any thought of how to come up with a solution?
    Reply With QuoteRegister To Reply
  11. 08-22-2016, 05:04 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: countifs in visible cell issues (filtering mode)

    As I am also monitoring the other forum, and know that you are getting ongoing help there, no, I won't be helping on this occasion as it would be a waste of my time. Sorry.
    Reply With QuoteRegister To Reply
  12. 08-22-2016, 05:30 AM #12
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    So let me get it straight...
    You give me a notice for crossposting and not mention it here, i do followed your orders to edit my posts and attach the link and moreover i apologize for not complying with forum's rules... And you think its a waste of your time???? Lol....Rude enough I guess???
    Reply With QuoteRegister To Reply
  13. 08-22-2016, 06:14 AM #13
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: countifs in visible cell issues (filtering mode)

    Quote Originally Posted by pepelepew View Post
    So let me get it straight...
    You give me a notice for crossposting and not mention it here, i do followed your orders to edit my posts and attach the link and moreover i apologize for not complying with forum's rules... And you think its a waste of your time???? Lol....Rude enough I guess???
    Not really. As I said, I know you are getting comprehensive help on the other forum. If you weren't I would offer help here. This is why flagging up that you have cross-posted is important, so that efforts are not duplicated. Try to see this from the point of view of those trying to help.
    Reply With QuoteRegister To Reply
  14. 08-22-2016, 06:19 AM #14
    pepelepew
    pepelepew is offline
    Registered User
    Join Date
    08-21-2016
    Location
    Greece
    MS-Off Ver
    2010
    Posts
    8

    Re: countifs in visible cell issues (filtering mode)

    Anyway,

    im of the opinion that two minds are better than one

    solution to first formula

    =SUMPRODUCT(SUBTOTAL(103;OFFSET(N9;ROW(N9:N10000)-ROW(N9);0;1));--(N9:N10000>0,5);--(O9:O10000>0,5))

    trying to figure out solution to the second one as its more complicated.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Reference Visible Cell after Filtering
    By mikesmartinez in forum Excel General
    Replies: 7
    Last Post: 02-26-2019, 03:23 AM
  2. [SOLVED] Using sumif or sumproduct with visible rows only in filter mode
    By YasserKhalil in forum Excel General
    Replies: 5
    Last Post: 07-07-2015, 02:32 PM
  3. Filtering column A then write text in visible cell B
    By noppornph in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-31-2015, 11:17 AM
  4. [SOLVED] COUNTIFS for visible cells only
    By bibu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-14-2014, 09:53 PM
  5. Table Object after filtering - Count visible rows & First visible row
    By limalf in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-13-2013, 07:29 PM
  6. Copy visible cells from Row B paste to Row A in filter mode
    By lakshmipathi123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2012, 03:37 PM
  7. Run excel in visible mode from aspnet
    By wakeup in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-14-2005, 10:08 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1