IF AND with multiple conditions
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?
Best Answer
-
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
-
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
-
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")))
- My first If check looks at US/Non-US and
- Then I check the case for C or GM and then E or ACQ..
- 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
-
Thanks @Kelly Moore! That worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!