Multiple if statements combined into 1

Stephanie Tran

Hi,

I am trying to create a formula to update the status column.

If ALL 5 people columns has any combination of "approved", "approved: with questions", "approved: with comments", then it marks the status Approved.

If any of the 5 people columns has "In Review" or Reviewed: with questions", the status column in mark as "In Review".

I am not sure how to build this the most efficient way.

KryF

Hi, you can try the steps below. Not sure if it works effectively, just a suggestion.

1) Put new column "Approval status" in the sheet

2) Create automation "Change cell value"

3)Inside automation workflow, consider "When rows are added or change" > when "Any Field" changes .....

4) Then, add "condition to filter row" where "Supervisor" column "is one of" Approved, approved with comments etc. -- "Add another condition" --and where "Manager" column "is one of" Approved, approved with comments etc.. and so forth

5) Change cell value "Select a column: Approval status" to Approved

6) Repeat to set automation for Review status.

Andrée Starå

Hi @Stephanie Tran

I hope you're well and safe!

Try something like this.

=
IF(COUNTIF(Supervisor@row:VP@row, CONTAINS("Approved", @cell)) = 5, "Approved", 
IF(CONTAINS("Review", Supervisor@row:VP@row), "In Review"))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Stephanie Tran

Thank you both for options.

@Andrée Starå your formula worked.

It is has saved me so much time.

Andrée Starå

@Stephanie Tran

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Stephanie Tran

@Andrée Starå

I would like to add another thing to this formula and can't seem to get the syntax correct. I added another column [Public] that is a check box option.

I would like my status column to change from approved to public if that box is check.

=IF([Public ]@row = 1, "Public")

Can I or how do I add that to my existing formula?

=IF(COUNTIF(Katrina@row:Maria@row, CONTAINS("Approved", @cell)) = 5, "Approved", IF(CONTAINS("Review", Katrina@row:Maria@row), "In Review"))

I hope this makes sense and thanks for any help.

Steph