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 ✓

    @Camille

    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")

Answers

  • Sarah_H
    Sarah_H ✭✭

    Hi @Camille

    Try this as a column formula in the Activity Indicator column. If I'm understanding your needs to correctly, you want that column to be automated based on the Status column.

    =IF(CONTAINS("Ongoing", Status@row), "Active", IF(CONTAINS("Stopped", Status@row), "Not Active", ""))

    Let me know if this helps!

  • Camille
    Camille ✭✭✭

    Hi @Sarah_H ,

    Thanks for getting back to me so quickly. That's not quite what I was looking for. For example, for all of Project 1, if at least one of the cells in the Status Column contains "Paused" or "Stopped", then the Activity Indicator would show Not Active for all Project 1 rows (first 4 rows).

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

    @Camille

    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")

  • Camille
    Camille ✭✭✭

    Hi @Leibel Shuchat ,

    That worked! Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!