Contains "Kansas" is returning TRUE when actually contains ARKANSAS

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

Kansas

Arkansas

Kansas and Arkansas

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! :)

Find more posts tagged with

Formulas

Accepted answers

Catherine Hall

OK! figured it out!

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

All comments

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

OK! figured it out!

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.