IF AND with multiple conditions

Kayla Q

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?

Kelly Moore

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

Kelly Moore

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

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

Kayla Q

Thanks @Kelly Moore! That worked!