Multiple IF Statements -- Status Updates

Hello,

I am trying to put together a sheet that reflects as a holistic "Status Update" depending on whether or not something has been "Approved", is "Pending", or has been "Denied".

I attached a photo that shows what I am trying to get my sheet to reflect and what is happening with returned values.

Here are the examples of what I want my formula to do:

IF: SR. Site Superintendent Approval? = YES & Sr. Project Manager Approval? = YES then Final Status will be "APPROVED"

IF one of these columns say "NO" at all I need to Final Status column to reflect "Denied"

If both of the columns say "Pending" I need it to reflect "Pending"

If one of the statuses reads "Pending" and one says "Yes" I still need it to reflect "Pending"

This is what I have been trying to get to work, but it keeps returning "0" in some of my columns, and also placing a "0" at the beginning of my "Denied" return.

Help?

=IF(AND([Sr. Site Superintendent Approval?]16 = "PENDING", [Sr. Project Manager Approval?]16 = "PENDING"), "PENDING") + IF(AND([Sr. Site Superintendent Approval?]16 = "YES", [Sr. Project Manager Approval?]16 = "YES"), "APPROVED") + IF(OR([Sr. Site Superintendent Approval?]16 = "NO", [Sr. Project Manager Approval?]16 = "NO"), "DENIED")

Also Note: I was trying to add rules about "Pending If one said "Pending" and one said "Yes" = "Pending" but the formula kept returning strange.

IF Statements.PNG

Find more posts tagged with

Construction

Formulas

Project Management

Accepted answers

Paul Newcome

Give something like this a try...

=IF(CONTAINS("no", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Denied", IF(CONTAINS("pending", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Pending"), IF(CONTAINS("yes", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Approved")))

All comments

Paul Newcome

Give something like this a try...

Julie_Boss85141

That didn't work exactly right for some reason, but I tried it like this and it immediately worked:

=IF(CONTAINS("NO", [Sr. Site Superintendent Approval?]7:[Sr. Project Manager Approval?]7), "DENIED", IF(CONTAINS("PENDING", [Sr. Site Superintendent Approval?]7:[Sr. Project Manager Approval?]7), "PENDING", IF(CONTAINS("YES", [Sr. Site Superintendent Approval?]7:[Sr. Project Manager Approval?]7), "APPROVED")))

Thanks for the help!!!

Paul Newcome

Hmm... That's odd. At least you got it to work. Happy to help!

Julie_Boss85141

Paul,

Is it normal for it to glitch like that and not work with the @row?

Paul Newcome

It is not, but it could have also been some other error that was not noticed at first but unintentionally fixed when removing the @row references. What error were you getting?

Paul Newcome

Was it either #UNPARSEABLE or #INCORRECT ARGUMENT?

I just noticed an extra closing parenthesis in mine just after "Pending". It may have been that you corrected that without even realizing it when removing the @row references.

Sue Emery

I have used your formula but when one column is yes and other column is no, the approval is yes instead of no. How do I get the Approval Status to change to no if one of the approval columns is no?

Here is the formula and a screen shot of sheet:

=IF(CONTAINS("Yes", [Meg Hummel approval]@row:[Thomas Payne approval]@row), "Yes", IF(CONTAINS("No", [Meg Hummel approval]@row:[Thomas Payne approval]@row), "No"))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.