multiple condition IF formula

Hi Community! I decided to ask for help after trying many variations for my formula with multiple conditions without any success. It does not give me error message, but when I validate the results, it just not correct. I will write in english what I want to achieve.

1. if order status contains 'work complete' or 'invoiced' return value "0"

2. if Main work center contains 'CANC' or 'Schedule' then return "0". But in any other case (only true for the 2nd condition), additionally if the System status contains "Closed" only then return "1"

Every other case (including condition 1) return value "1"

if (!Contains([Order Status],"Work Complete","Invoiced") && Contains([Main work center], "CANC","SCHEDULE")) THEN "0" ELSE "1" ENDIF - I got until this point but it already didnt work...without including the System status condition.

I hope it is understandable , and someone can help me resolve this.

I really appreciate the support in advance!

regards,

Error Message

Accepted answers

RolandSchubert

Hi @EtamSoko ,

I think, the main task is to sort out the priority of the conditions. My understanding is:

Condition 1: [Order Status] = 'Invoiced' or [Order Status] = 'Work complete'

Condition 2: [Main Work Center] = 'CANC' or [Main Work Center] = 'Schedule'

Condition 3: [System Status] = 'Closed'

If condition 1 is met, then result is 0, independent of other conditions

In condition 1 is not met, but condition 2 is met, result is also 0 (again independent of other conditions)

If neither condition 1 nor condition 2 is met and condition 3 is met, then 1

In any other case: 1 (condition 3 makes no sense in my opinion, as it has no impact)

Sample input data could be like this:

and formula would be:

Hope this is helpful in any way, at least to "organize" the conditions.

Best,

Roland

MultipleIfs.yxmd

All comments

wdavis

Hi @EtamSoko

For your Contains formulas, you will need to separate these out for each condition e.g.

if (!Contains([Order Status],"Work Complete") AND !Contains([Order Status], "Invoiced")

Let me know if this fixes it for you. Otherwise, if you want to provide a small sample of the table we can help build this for you

Thanks

Will

kelsey_kincaid

Hi @EtamSoko ,

Here's a rough example of what I think you're looking for. I assumed in the below formula that if [System Status]='Closed' then it wouldn't matter what other conditions were fulfilled, you would just want it to be 1. If that's not the case then let me know and I can help you adjust.

IF [System Status]="Closed" THEN 1 ELSEIF

Contains([Order Status],"Work Complete") OR Contains([Order Status],"Invoiced") THEN 0

ELSEIF Contains([Main Work Center],"CANC") OR Contains([Main Work Center],"Schedule") THEN 0

ELSE 1 ENDIF

EtamSoko

hi Kayers,

this looks I believe really good but I have a few problems with it when I run.

First of all let's disregard the System status condition entirely.

Regarding the rest of the formula, when I put into the same formula box, either to every row returns 0 value, or some rows just simply incorrect - after validating them 1 by 1.

So I figured I am going to put into 2 separate box by splitting the conditions based on the field that it the formula is looking at (order status,main work center) but it also doesn't work properly because 1 overwrites the other.

I am a bit confused what should I change in the formula. I might add more to the query that I missed to mention before. First of all the formula should look at the Order status field if there is Work complete or Invoiced. If none of the above is true then look at the Main Work center column and return 0 or 1 based on the formula. The reason for this is that Order status column might be Null sometimes, so then I need the formula to look at the Main Work Center column.