Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Loop Through Visible Columns within Range

  1. #11
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Something like

    =COUNTIF(E2:E599,"<="&(TODAY()+30))

    might do it, I'm not sure that I follow the purpose of your existing formula.

  2. #12
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    It would be go much easier to PM the spreadsheet to you in order to explain as I'm unable to post a picture URL etc.
    To further complicate matters, there are some courses that don't have an expiry date so they will be picked up by the CountIf formula as well.

  3. #13
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,050
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    You could upload a sample workbook to a site such as dropbox.com then put the link here.


  4. #14
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Quote Originally Posted by aoifew View Post
    To further complicate matters, there are some courses that don't have an expiry date so they will be picked up by the CountIf formula as well.
    That is another common misconception, there is usually a way to ignore 0 values (i.e. those with no expiry date).

    A sample uploaded to a file share site as Akuini suggested would help significantly.

  5. #15
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Good thinking Akuini. Hope this works https://www.dropbox.com/s/bgdzp3yzob...mple.xlsx?dl=0


  6. #16
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    See if this does what you need, using your sample file,

    D13, array confirmed with Ctrl Shift Enter (see https://exceljet.net/glossary/cse )

    =SUM(IFERROR((D6:D10<(TODAY()-$C$6:$C$10))*(D6:D10<>""),0))

    D14, array confirmed with Ctrl Shift Enter

    =SUM(IFERROR((D6:D10<(TODAY()+30-$C$6:$C$10))*(D6:D10<>""),0))-D13

    D15

    =COUNT(D6:D10)-SUM(D13:D14)

    D16

    =COUNTIF(D6:D10,"=")

    Once entered, they can all be dragged right as needed to fill the other columns.

  7. #17
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Thanks so much Jason, I'll give this a go tomorrow

  8. #18
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Awesome, absolutely awesome!!! Can't thank you enough Jason

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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