Formula for Status signs based on multiple columns
Answers
-
@Paul Newcome I have this formula to change the Status column depending on three approval columns. It works but I want it to do the same thing for denied.
Status formula:
=IF(AND([TUSC Operations Approval]@row = "Approved", [Customer Experience Approval]@row = "Approved", [Demo Host Approval]@row = "Approved"), "Confirmed", "Pending")
How can I have both in the same formula?
So it should be if all three have "Denied", then show "Denied, if not all three then show "Pending"
-
@Adrian Garcia Try this...
=IF(AND([TUSC Operations Approval]@row = "Denied", [Customer Experience Approval]@row = "Denied", [Demo Host Approval]@row = "Denied"), "Denied", IF(AND([TUSC Operations Approval]@row = "Approved", [Customer Experience Approval]@row = "Approved", [Demo Host Approval]@row = "Approved"), "Confirmed", "Pending"))
-
@Paul Newcome Hi Paul,
I have 6 approval columns next to each other and not all are in the approval process for every event. So, in some cases 3 out of 6 approval cells will be blank, in some cases all will be involved. I want my formula to ignore blank cells and decide if the event is approved or not.
If we use this one as a base for example: Sometimes operations is not part of approval but other 2 are. =IF(AND([TUSC Operations Approval]@row = "Denied", [Customer Experience Approval]@row = "Denied", [Demo Host Approval]@row = "Denied"), "Denied", IF(AND([TUSC Operations Approval]@row = "Approved", [Customer Experience Approval]@row = "Approved", [Demo Host Approval]@row = "Approved"), "Confirmed", "Pending"))
How can I modify my formula?
Thanks for your answer :)
-
@Ayperi Uzun If they are all right next to each other and we are ignoring blanks, you can use something along these lines...
=IF(COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell = "Denied") + COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell <> ""), "Denied", IF(COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell = "Approved") + COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell <> ""), "Approved", "Pending"))
Basically we count how many are "Denied" and compare that to how many are not blank. If those numbers match then all non-blank cells must be "Denied". Then we use that same logic for "Approved" and finally everything else is "Pending".
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!