complex if/and/or question

Kenneth Green

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.

bcwilson.ca

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

bcwilson.ca

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

Kenneth Green

thanks, I will try this today