complex if/and/or question
I have 5 status columns, each with 4 possible values (Approved, Approved with Comments, N/A, Resend). I want to fill in an "Overall" Status
The 5 columns are
__________________
Tagless Status
Front Graphic Status
Back Graphic Status
Right Sleeve Status
Left Sleeve Status
Other Status
My logic is If all of the columns are Approved, Approved with Comments, or N/A I want the Overall status to by Approved (actually a 1 because it is a checkbox), If any of the columns are not filled in or Resends then I want the status checkbox not filled in.
Best Answer
-
My trick is to create a series of helper columns that will help you write the if case.. When it is done then you can use a text editor to copy and paste the formulas into each other to create a massive if statement.
But let's look at your logic first..
- Basically, you want to fail (cause a zero) in two cases.. All others cause a sucess
- If there is a Blank in the cell or If there is a "Resend" in the cell
- It is much easier to look for failure cases than for the success cases.
- Also nested if/and/or will drive you nuts
For the purpose of below a "0" is a Fail Case and a "1" is a success Case
- So First case a blank cell.. If any cell is blank then it should cause a "Fail" or a Zero. So rather then a big nested If why don't we just count the empty cells.. and if it is more then Zero we have a "fail case"
CASE 1 => IF(COUNTIF([Tagless Status]@row:[Other Status]@row, "") > 0), 0, 1)
- Next, if there is a "Resend" it fails as well.. Well let us count those and if we have more then Zero then we
CASE 2 => IF(COUNTIFS([Tagless Status]@row:[Other Status]@row, "Resend") > 0), 0, 1)
- Now we could do something silly like add those together but then you may get a "2" and that is not going to work for a checkbox or flag.
- So this is where you can put the "OR" in place. We will create an IF statement that says "If either case is greater then zero then we have a FAIL aka 0 otherwise, we have to have a PASS aka 1:
IF(OR(CASE1 > 0 , CASE2 > 0), 0, 1)
- In Summary:
=IF(OR(COUNTIFS([Tagless Status]@row:[Other Status]@row, "Resend") > 0, COUNTIF([Tagless Status]@row:[Other Status]@row, "") > 0), 0, 1)
You can see I used some "helper columns" to check my logic first. I then pasted the working formula into the Overall Approved column...
Then you can simply delete the Helper Columns
Hope that helps and makes sense
Brent Wilson
- Basically, you want to fail (cause a zero) in two cases.. All others cause a sucess
Answers
-
My trick is to create a series of helper columns that will help you write the if case.. When it is done then you can use a text editor to copy and paste the formulas into each other to create a massive if statement.
But let's look at your logic first..
- Basically, you want to fail (cause a zero) in two cases.. All others cause a sucess
- If there is a Blank in the cell or If there is a "Resend" in the cell
- It is much easier to look for failure cases than for the success cases.
- Also nested if/and/or will drive you nuts
For the purpose of below a "0" is a Fail Case and a "1" is a success Case
- So First case a blank cell.. If any cell is blank then it should cause a "Fail" or a Zero. So rather then a big nested If why don't we just count the empty cells.. and if it is more then Zero we have a "fail case"
CASE 1 => IF(COUNTIF([Tagless Status]@row:[Other Status]@row, "") > 0), 0, 1)
- Next, if there is a "Resend" it fails as well.. Well let us count those and if we have more then Zero then we
CASE 2 => IF(COUNTIFS([Tagless Status]@row:[Other Status]@row, "Resend") > 0), 0, 1)
- Now we could do something silly like add those together but then you may get a "2" and that is not going to work for a checkbox or flag.
- So this is where you can put the "OR" in place. We will create an IF statement that says "If either case is greater then zero then we have a FAIL aka 0 otherwise, we have to have a PASS aka 1:
IF(OR(CASE1 > 0 , CASE2 > 0), 0, 1)
- In Summary:
=IF(OR(COUNTIFS([Tagless Status]@row:[Other Status]@row, "Resend") > 0, COUNTIF([Tagless Status]@row:[Other Status]@row, "") > 0), 0, 1)
You can see I used some "helper columns" to check my logic first. I then pasted the working formula into the Overall Approved column...
Then you can simply delete the Helper Columns
Hope that helps and makes sense
Brent Wilson
- Basically, you want to fail (cause a zero) in two cases.. All others cause a sucess
-
thanks, I will try this today
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!