Help

Conditional formula for multi-select cell

Topic Labels: Formulas
645 1
cancel
Showing results for 
Search instead for 
Did you mean: 
SharonC
5 - Automation Enthusiast
5 - Automation Enthusiast

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" 

SharonC_1-1702442930170.png

Thank you!!

1 Reply 1
joshsorenson
6 - Interface Innovator
6 - Interface Innovator

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