Collect Values from a Group and See if it Contains a Certain Value

I have a sheet in which the person in charge of a project is responsible for providing weekly updates. As part of the update, they need to indicate the status of the project (ongoing, paused, stopped). I want to have a column that looks through the Status column for a given Project. If one of the rows contains "Stopped" or "Paused", I want the column to say "Not Active". Otherwise, it will show "Active" as shown in the highlighted column. This will make it easier for me to filter Active programs on my report and dashboard. How can I do this? I think I would end up using the COLLECT function as the inner function =COLLECT(Status:Status, Project:Project, Project@row). But I'm not sure what the outer function should be.

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓


    Seems like you are looking for the below:

    =IF(OR(CONTAINS("Stopped", COLLECT(Status:Status, Project:Project, Project@row)), CONTAINS("Paused", COLLECT(Status:Status, Project:Project, Project@row))), "Not Active", "Active")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!