IF AND with combination of does not equal and does equal

melimob
melimob ✭✭✭
edited 06/23/22 in Formulas and Functions

Hi I have 3 columns all containing country names.

For each row, I want to do a nested statement to show:

If all 3 countries (columns) = "UAE" then put "Local Servicing - UAE"

*If all 3 countries are the same then put "Local Servicing"

*If first 2 columns are UAE but column 3 is not then put "HUB"

otherwise put "HUB & Spoke",

I had some of it working but can't get those with an asterix to work? Any ideas please?:

Below doesn't work?

=IF(LEVEL@row = "DK", IF(AND([Servicing Country]@row = "UAE", [Fulfilment Country]@row = "UAE", [Employee Country]@row = "UAE"), "LOCAL SERVICING - UAE"), IF(AND([Employee Country]@row <> "UAE", [Servicing Country]@row = "UAE", [Fulfilment Country]@row = "UAE"), "HUB"))

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    I am a bit confused by the logic. It is important to have it clear before you start developing the IF statement.

    I don't know what First2 columns are related to [Fulfilment Country], [Employee Country],[Servicing Country] and there is no mention of LEVEL

    I have looked at your logic and think I understand it this way.

    It is best to rule out conditions first and then try to account for them all at the end

    • If Column1 and Column2 are not the same then you always have "HUB and Spoke"
    • If Column3 does not the same as Column1 (or 2) then we have "HUB"
    • If Column 3 is UAE then you have "Local Servicing - UAE" otherwise "Local Servicing"

    Try this

    =IF(NOT(Column1@row = Column2@row),"Hub & Spoke",IF(NOT(Column1@row = Column3@row),"HUB",IF(Column3@row = "UAE","Local Servicing - UAE","Local Servicing")))


    Breaking it down

    First Check for Different Countries in Column1 and Column2

    IF(NOT(Column1@row = Column2@row),"Hub & Spoke",

    Second Check that Column3 doesn't match Column2 (We know Column1 = Column2 at this point so it is arbitrary which one we pick)

    IF(NOT(Column1@row = Column3@row),"HUB",

    Third Check for UAE (as we now know Col3 is the same as Col1 and Col2)

    IF(Column3@row = "UAE","Local Servicing - UAE","Local Servicing")))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!