I need to skip blank columns in rows
This is my current formula:
=IF(AND([BO1 approval]@row = "Approved", [BO2 approval]@row = "Approved", [BO3 approval]@row = "Approved"), "Approved", IF(OR([BO1 approval]@row = "Denied", [BO2 approval]@row = "Denied", [BO3 approval]@row = "Denied"), "Denied", "Submitted"))
How it works:
If all approvers hits "Approved" =Approved, if any one of them hits"Denied" then it's denied.
Issue: In some cases, all of the approval columns won't be filled. e.g.
BO1 (Bob) - Approved
BO2 (blank) - blank
BO3 (Sara) - Approved
However, I'd like the same logic to apply.
Best Answers
-
Hi @Tsega Thompson ,
Try this:
=IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted"))
Let me know if it works!
Best,
Heather
-
@Tsega Thompson Oh, yes! Here you go:
=IFERROR(IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted")),"")
Answers
-
Are all of your approval columns next to each other or are there columns in between that could also contain "Denied" or "Approved"?
-
Hi @Tsega Thompson ,
Try this:
=IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted"))
Let me know if it works!
Best,
Heather
-
Hi Paul,
They are all next to each other.
-
Thanks Heather that seemed to do it!
-
Unfortunately, that fixed something and broke something else. If Bo1-3 are blank I get #DIVIDE BY ZERO in that column.
However, that column feeds a function in another column. Is it possible to make it blank instead?
-
@Tsega Thompson Oh, yes! Here you go:
=IFERROR(IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted")),"")
-
@Heather D That's pretty close to the route I was going to take, but I wanted to make sure that there were no columns in between that could skew the results.
-
@Paul Newcome I figured, but I apparently threw caution to the wind today 😁
-
that worked. Thanks again!
-
@Heather D Hahaha. Living on the edge.
I personally read it this way (@Tsega Thompson please correct me if I am wrong)... There are three possibilities for a cell. "Denied", "Approved", and blank. There is the possibility of an "Approved" and two blanks, but in that instance we want it to say "Approved", but "Denied" takes priority.
I would have gone with a CONTAINS instead of the COUNTIFS (but it is the same idea of looking across the range for at least one instance).
=IF(CONTAINS("Denied", [BO1 approval]@row:[BO3 Approval]@row), "Denied", IF(CONTAINS("Approved", [BO1 approval]@row:[BO3 Approval]@row), "Approved"))
Of course if there is in fact a "Submitted" status, then we would need to throw that into the mix...
=IF(CONTAINS("Denied", [BO1 approval]@row:[BO3 Approval]@row), "Denied", IF(CONTAINS("Submitted", [BO1 approval]@row:[BO3 Approval]@row), "Submitted", "Approved"))
-
@Paul Newcome Ooh! I like that one better. Anything to make a formula more concise!!
-
@Heather D It is basically the same concept but with a different approach. Searching the range for a key word.
-
Just curious how would it have worked if the columns weren't in a range?
-
You would need to use OR statements in place of the contains and specify each cell.
=IF(OR([B01 Approval]@row = "Denied", [B02 Approval]@row = "Denied", [B03 Approval]@row = "Denied"), "Denied", IF(OR(..............................), "Submitted", "Approved"))
It isn't too bad with only two or three columns, but much more than that can become rather tedious.
-
noted with thanks.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!