IF AND with multiple conditions

Options

I've got a doozie for you all.

Luckily, I'm partially there.

I want to run a function with the following conditions:

If Task = C or GM, and JXDN = US, then return Trademark/Domain General - US

If Task = E or ACQ, and JXDN = US, then return Trademark Disputes - US

If Task = P, and JXDN = US, then return Trademark Prosecution - US

If Task = C or GM, and JXDN = not US, then return Trademark/Domain General - Foreign

If Task = E or ACQ, and JXDN = not US, then return Trademark Disputes - Foreign

If Task = P, and JXDN = not US, then return Trademark Prosecution - Foreign

The current formula I am using is getting me the first part of my desired outcome, without returning the "US" or "foreign" part. It is as follows:

=IF(Task@row = "E", "Trademark Disputes - ", IF(Task@row = "C", "Trademark/Domain General - ", IF(Task@row = "ACQ", "Trademark Disputes - ", IF(Task@row = "GM", "Trademark/Domain General - "))))

I am basing my design off of an already existing Excel formula:

IF(U13="E","Trademark Disputes - ",IF(U13="P","Trademark Prosecution - ",IF(U13="C","Trademark/Domain General - ",IF(U13="ACQ","Trademark Disputes - ",IF(U13="WN","Trademark Disputes - ",IF(U13="GM","Trademark/Domain General - ",""))))))&IF(T13="US","US","Foreign")

I don't think Smartsheet plays well with the "&" in this Excel formula

Can someone point me in the right direction?

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Kayla Q

    Here is the formula you requested

    =IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row = "US"), "Trademark/Domain General - US", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row = "US"), "Trademark Disputes - US", IF(AND(Task@row = "P", JXDN@row = "US"), "Trademark Prosecution - US", IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row <> "US"), "Trademark/Domain General - Foreign", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row <> "US"), "Trademark Disputes - Foreign", IF(AND(Task@row = "P", JXDN@row <> "US"), "Trademark Prosecution - Foreign"))))))


    Is there a chance that your JXDN field could be blank? If yes then you will need this formula instead

    =IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row = "US"), "Trademark/Domain General - US", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row = "US"), "Trademark Disputes - US", IF(AND(Task@row = "P", JXDN@row = "US"), "Trademark Prosecution - US", IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row <> "US", JXDN@row <> ""), "Trademark/Domain General - Foreign", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row <> "US", JXDN@row <> ""), "Trademark Disputes - Foreign", IF(AND(Task@row = "P", JXDN@row <> "US", JXDN@row <> ""), "Trademark Prosecution - Foreign"))))))

    Do either of these work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Kayla Q

    Here is the formula you requested

    =IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row = "US"), "Trademark/Domain General - US", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row = "US"), "Trademark Disputes - US", IF(AND(Task@row = "P", JXDN@row = "US"), "Trademark Prosecution - US", IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row <> "US"), "Trademark/Domain General - Foreign", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row <> "US"), "Trademark Disputes - Foreign", IF(AND(Task@row = "P", JXDN@row <> "US"), "Trademark Prosecution - Foreign"))))))


    Is there a chance that your JXDN field could be blank? If yes then you will need this formula instead

    =IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row = "US"), "Trademark/Domain General - US", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row = "US"), "Trademark Disputes - US", IF(AND(Task@row = "P", JXDN@row = "US"), "Trademark Prosecution - US", IF(AND(OR(Task@row = "C", Task@row = "GM"), JXDN@row <> "US", JXDN@row <> ""), "Trademark/Domain General - Foreign", IF(AND(OR(Task@row = "E", Task@row = "ACQ"), JXDN@row <> "US", JXDN@row <> ""), "Trademark Disputes - Foreign", IF(AND(Task@row = "P", JXDN@row <> "US", JXDN@row <> ""), "Trademark Prosecution - Foreign"))))))

    Do either of these work for you?

    Kelly

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 07/09/22
    Options

    Try this

    =IF([JXDN]@Row = "US", IF(OR([Task]@Row="C",[Task]@Row="GM"), "Trademark/Domain General - US", IF(OR([Task]@Row="E",[Task]@Row="ACQ"), "Trademark Disputes - US", "Trademark Prosecution - US")), IF(OR([Task]@Row="C",[Task]@Row="GM"), "Trademark/Domain General - Foreign", IF(OR([Task]@Row="E",[Task]@Row="ACQ"), "Trademark Disputes - Foreign", "Trademark Prosecution - Foreign")))

    1. My first If check looks at US/Non-US and
    2. Then I check the case for C or GM and then E or ACQ..
    3. I assume what is left over is the "P" condition so it doesn't need to be checked and is assumed to be everything else

    Then just put it all together

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

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Kayla Q
    Kayla Q ✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!