How does COLLECT() with String Criterion Work?

I have a simple formula (I thought) that seems to broken. It seems to be centered around the existence of a String in the Criterion Parameter of COLLECT().

Formula: =IF(MIN(COLLECT(Date:Date, [Work Type]:[Work Type], "Mandatory"))=Date1, Hours1-6, Hours1)


Is it not possible to have a string Criterion like "Mandatory" in COLLECT()?


Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Are you using the correct column headers? The formula works for me, to the degree that it parses and returns a value. There is no issue putting a string in text so long as it is surrounded by quotes as you've done.

    I'll add it's not fully clear what you are trying to accomplish with this formula. If you can explain the desired functionality I could potentially give a more useful response.

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!