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

Camille

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.

Leibel S

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

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

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

@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

Hi @Leibel Shuchat ,

That worked! Thank you.