Contains "Kansas" is returning TRUE when actually contains ARKANSAS
My State field is multi-select and users can select:
Kansas
or
Arkansas
or
Kansas and Arkansas
or
other.
I want to return a text value "Is Kansas" in a separate column if the State column contains Kansas or contains Kansas and Arkansas but will not return "Is Kansas" if the State column has only Arkansas (or other).
When I try a "Contains" formula it happily returns TRUE if it finds Arkansas.
When I try a COUNTIF to see how many kansases it finds, it only returns "1" if there is only one selection and that one selection happens to be Kansas.
When I try FIND, it just gives me "0" unless , again, it's just one Kansas and not multiple states.
States may or may not be in order alphabetically. And there may be 1 or up to 50 states in any given cell.
I just want to be able to detect when Kansas exists.
Any help/suggestions are GREATLY appreciated! :)
Best Answer
-
OK! figured it out!
=IF(FIND("KANSAS", SUBSTITUTE([State/Territory]@row, "ARKANSAS", "ARK")) > 0, 1, 0)
Answers
-
I have this at the moment:
=IF(AND(CONTAINS("Kansas", [State/Territory]@row), NOT(CONTAINS("Arkansas", [State/Territory]@row))), 1, 0)
which works on all cells except the ones that contain Kansas AND Arkansas.
If I add another condition to allow Kansas and Arkansas:
=IF(OR(AND(CONTAINS("Kansas", [State/Territory]@row), NOT(CONTAINS("Arkansas", [State/Territory]@row))), AND(CONTAINS("Kansas", [State/Territory]@row), CONTAINS("Arkansas", [State/Territory]@row))), 1, 0)
now I get false positives when only Arkansas is present (no Kansas).....
-
OK! figured it out!
=IF(FIND("KANSAS", SUBSTITUTE([State/Territory]@row, "ARKANSAS", "ARK")) > 0, 1, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!