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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!