Collecting earliest date based on certain criteria

Hey Smartsheet,

I'm trying to pull the earliest date that a status symbol was Red or Yellow unless that date is followed by a Green status symbol and then a Red or Yellow status symbol. In that scenario, I would want the Red/Yellow date after the Green date to be shown in the Roll-up Sheet.


In the example below, the Roll-Up Sheet should be showing 11/24 instead of 11/2 but I cannot find out what formula (or if its even possible).

Data Table


Roll-up Sheet


Thank you

Best Answers

  • Jeffrey Homme
    Jeffrey Homme ✭✭✭
    edited 12/01/20 Answer ✓

    @Leibel S


    That worked, thank you!! One additional question.


    For that formula , what statement would I add to have it pull based on the ID in my roll-up sheet?


    In the example below the formula you provided would pull 12/2/20 for IT-0341 and 11/20/20 for IT-0342.



    I've tried nesting an IF statement "ID@row" into the summary sheet to pull from the source sheet but I cant get the formula to not break.



Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @Jeffrey Homme Try:

    =LARGE(COLLECT(Date26:Date29, Status26:Status29, <>"green"), 1)

    where row 26 is top of range, 29 is bottom of range. This basically shows the most recent date for a row that is not green in status. (The formula returns a date so it needs to be in a date column.)

    That should meet this scenario. But I am not sure about the IF, THEN wrinkles you imply with the unless statement.

    Cheers,

    Dale

  • @DMurphy

    Thank you for the response. What I'm trying to do is pull the earliest date the Status was in Red/Yellow unless it was followed by a date in Green. I've tried every COLLECT, MIN/MAX, IFS that I can think of but cant seem to get the results shown below. Below are two examples on what I'm trying to do.


    Scenario A = 11/4/20


    Scenario B = 11/29/20


  • Jeffrey Homme
    Jeffrey Homme ✭✭✭
    edited 12/01/20 Answer ✓

    @Leibel S


    That worked, thank you!! One additional question.


    For that formula , what statement would I add to have it pull based on the ID in my roll-up sheet?


    In the example below the formula you provided would pull 12/2/20 for IT-0341 and 11/20/20 for IT-0342.



    I've tried nesting an IF statement "ID@row" into the summary sheet to pull from the source sheet but I cant get the formula to not break.



  • @Leibel S


    Thank you for both formulas, you have helped me so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!