Conditional formula for multi-select cell

[SharonC]

Hi All

I am trying to build a formula to reflect Master project status based on (sub)project status. Here's what I have. So the hierarchy should be:

1. If ANY of the project status contains "ongoing", then master project status to reflect "ongoing"
2. If "ongoing" does not exist and any of the project status is "planned", then master project status to reflect "planned"
3. If "ongoing" and "planned" do not exist and ALL of the project status is "hold", then master project status to reflect "hold"
4. The remaining should reflect as "concluded"

So in the below table, the correct reflection should be:

1. Row 1, 3, 4, 6, 8 should be "ongoing" (because "ongoing" applies to any)
2. Row 5, 7 should be "planned"
3. Row 2, 9, 10 should be "concluded" 

Thank you!!

[joshsorenson]

Hey Sharon, 

Might try this:

IF(
FIND("ongoing", {Project Status}) > 0,
"ongoing",
IF(
FIND("planned", {Project Status}) > 0,
"planned",
IF(
AND(
FIND("hold", {Project Status}) > 0,
NOT(FIND("ongoing", {Project Status})),
NOT(FIND("planned", {Project Status}))
),
"hold",
"concluded"
)
)
)