Multiple if statements combined into 1

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.

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.