Contains "Kansas" is returning TRUE when actually contains ARKANSAS


My State field is multi-select and users can select:





Kansas and Arkansas



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


  • Catherine Hall

    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).....

  • Catherine Hall
    edited 11/09/20 Answer ✓

    OK! figured it out!

    =IF(FIND("KANSAS", SUBSTITUTE([State/Territory]@row, "ARKANSAS", "ARK")) > 0, 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!