Can't get all IF AND parts of formula to work
Hi, I am creating a multi-part IF AND formula. Basically, I'm looking at two columns: Product and State
IF Product = Science, assign to Mike Foster
IF Product = Mathematics AND State = certain states, assign to Paula Smith
If Product = Mathematics AND State = other certain states, assign to Bill Peace
I've come up with the formula below; however, it's only recognizing the first and second IF statements, not the third. It's assigning ALL Mathematics Products to Paula Smith, none to Bill Peace regardless of the State. It's almost like it's not accounting for the State. What am I doing wrong? Am I listing the states incorrectly?
Adding: the Product and State columns are Text/Number. Also, I've tried changing AND to OR in both, and I've tried re-ordering the statements, and it still only recognizes Mike Foster and Paula Smith.
=IF(Product@row = "Science", "Mike Foster", IF(AND(Product@row = "Mathematics", [State]@row = "AK, WA, OR"), "Paula Smith, IF(AND(Product@row = "Mathematics", [State]@row = "PA, CA"), "Bill Peace")))
Answers
-
You need to add in some OR statements where you have multiple states such as this formula below:
=IF(Product@row = "Science", "Mike Foster", IF(AND(Product@row = "Mathematics", OR([State1]@row = "AK", [State1]@row = "WA", [State1]@row = "OR")), "Paula Smith", IF(AND(Product@row = "Mathematics", OR([State1]@row = "PA", [State1]@row = "CA")), "Bill Peace")))
You can do the OR portion first in the AND bracket if you prefer:
=IF(Product@row = "Science", "Mike Foster", IF(AND(OR([State1]@row = "AK", [State1]@row = "WA", [State1]@row = "OR"), Product@row = "Mathematics"), "Paula Smith", IF(AND(OR([State1]@row = "PA", [State1]@row = "CA"), Product@row = "Mathematics"), "Bill Peace")))
Hopefully this helps, post if you're unsure on anything.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!