Multiple IF ANDS criteria
Hello!
I'm trying to write a function for 3 criteria with if ands, "Approved", "Not Approved" and "Pending". Not sure im using if and correctly.
It's a document approval process which requires 5 individuals to review a document. I want the status of each document request to be shown per individual and then have the overall document status sjown.
For example
If Person A = approved, Person B= approved, Person C= approved, Person D= approved and Person E= approved then the overall status shown is "Approved".
If any of them "declined" the document then the overall status is shown as "Not approved"
If they haven't either approved or declined but its "submitted" then overall status should show "Pending".
I can't make the formula work for some reason.
- When all individuals approve i do get the "approved" status, however anything else is shown as "Pending",
=IF(AND([Person A]1 = "Approved", [Person B]1 = "Approved", [Person C]1 = "Approved", [Person D]1 = "Approved", [Person E]1 = "Approved"), "Approved", IF(AND([Person A]1 = "Declined", [Person B]1 = "Declined", [Person C]1 = "Declined", [Person D]1 = "Declined", [Person E]1 = "Declined"), "Not Approved", "Pending"))
Thank you!
Best Answer
-
Hey @jenniferrdz
Will this work for you?
=IF(AND([Person A]1 = "Approved", [Person B]1 = "Approved", [Person C]1 = "Approved", [Person D]1 = "Approved", [Person E]1 = "Approved"), "Approved", IF(OR([Person A]1 = "Declined", [Person B]1 = "Declined", [Person C]1 = "Declined", [Person D]1 = "Declined", [Person E]1 = "Declined"), "Not Approved", "Pending"))
If you don't need to specifically reference Row1 then change each reference row 1 to @cell (replace the 1 with @cell, example [Person A]@row) to make your formula more robust.
Kelly
Answers
-
Hey @jenniferrdz
Will this work for you?
=IF(AND([Person A]1 = "Approved", [Person B]1 = "Approved", [Person C]1 = "Approved", [Person D]1 = "Approved", [Person E]1 = "Approved"), "Approved", IF(OR([Person A]1 = "Declined", [Person B]1 = "Declined", [Person C]1 = "Declined", [Person D]1 = "Declined", [Person E]1 = "Declined"), "Not Approved", "Pending"))
If you don't need to specifically reference Row1 then change each reference row 1 to @cell (replace the 1 with @cell, example [Person A]@row) to make your formula more robust.
Kelly
-
@Kelly Moore it worked! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 501 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 79 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!