Return a Minimum Date with A Condition

Hi there

I am trying to adjust summary formulas so that they only return information from rows considered In Scope (so that data does not have to be removed from Not In Scope rows).

Can anyone help with a formula that would return the MIN date from this group, only for the rows In Scope, ie Out of Scope is checked / true.

Thanks in advance

Emma

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will need to incorporate the COLLECT function.

    =MIN(COLLECT(CHILDREN(), CHILDREN([Not In Scope]@row), @cell <> 1))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks so much for coming back to me Paul. Is there a reason why this returns no value?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you have any blanks in any of the child rows? Technically a blank is "less than" a date, so the MIN function would return the blank. to avoid this, we can add another range/criteria set to the COLLECT function to ignore blanks and only pull from cells that are not blank.

    =MIN(COLLECT(CHILDREN(), CHIDLREN(), @cell <> "", CHILDREN([Not In Scope]@row), @cell <> 1))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Paul

    No blanks, but I do get a different result with that formula this time - #UNPARSEABLE.

    To confirm, what I want the formula to do is return the Min Scheduled Start Date to me for [Pre requisites of the Workshop], but only for In Scope Items, ie all those not checked [Not In Scope], so I would expect the result to be 05/08/21.

    Am I asking the question the wrong way around? I didn't want to make [Not In Scope] [In Scope] because not many things will be Out of Scope.

    Thank you for your help with this.

  • I think I've cracked it!

    =MIN(COLLECT(CHILDREN(), CHILDREN([Not In Scope]@row), @cell = 1 = 0))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The way I was writing it with "@cell <> 1" is basically saying the boxes that were not checked. I am not sure why it was not working for you as I have used this quite a few times in the past.


    I'm glad you got it figured out though!

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!