How does COLLECT() with String Criterion Work?

Nicholas Mathern
Nicholas Mathern ✭✭
edited 08/26/20 in Formulas and Functions

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)


Result: #UNPARSEABLE


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

Tags:

Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar Community Champion
    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.

    image.png

    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.

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/πŸ’‘Insightful, ⬆️ Vote Up, ❀️Awesome!

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar Community Champion
    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.

    image.png

    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.

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/πŸ’‘Insightful, ⬆️ Vote Up, ❀️Awesome!

  • Nicholas Mathern
    Nicholas Mathern ✭✭

    Dan,


    I've spent years of my life being careful with formulas like this making sure every character is precisely what it should be... and yet, that must have been my problem here.

    I promise I tried re-typing everything several times in several different simpler versions before I came here. Perhaps it was a passing bug or I just needed to take a walk outside? Hah!


    Thanks for convincing me it was worth another try!


    Regards,

    Nick M.

  • SoS | Dan Palenchar
    SoS | Dan Palenchar Community Champion

    @Nicholas Mathern it happens to the best of us! Happy to help.

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/πŸ’‘Insightful, ⬆️ Vote Up, ❀️Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!