How to capture a value from multi-select column

Which formula works better to find out if a value exists in a multi select dropdown column? I have responses collected from a survey and one of the column was multi-select dropdown list type column. I'm trying to separate out the selection values and adding them in a separate sheet (so using cross sheet reference).

Have tried following variations so far, and they only worked for the first value from the multi select column

=COUNTIFS({Email}, {Email} = Email@row, {committee selection}, CONTAINS("Business/ Content", {committee selection}))

=COUNTIFS({Email}, {Email} = Email@row, {committee selection}, HAS("Business/ Content", {committee selection}))

=COUNTIFS({Email}, {Email} = Email@row, {committee selection}, {committee selection} = "Business/ Content")

Find more posts tagged with

Sheets

Formulas

Accepted answers

David Joyeuse

@ameyaa

The example given in the HAS function page is if the column being tested is a string text. It should be working fine on a dropdown list.

Another option to try would be the FIND function, where you can only check for a string of 3/4 characters that is unique to each value would do the trick too.

=COUNTIFS({Email}, Email@row, {committee selection}, FIND("ss/ C",@cell)>0)

Should return all Business/ Content items as long as there is not another item with the "ss/ C" string within it :)

If I remember correctly, items in multi dropdown list are separated by two spaces or CHAR(10).

Hope it helped!

All comments

Tracy PIneda

I am trying to do the exact same thing and just emailed support. There must be a way!!

David Joyeuse

Hi @ameyaa & @Tracy PIneda

Since you're going with multi select dropdown list, you should use HAS as it's specificly designed for this.

So use this:

=COUNTIFS({Email}, Email@row, {committee selection}, HAS("Business/ Content",@cell))

Make sure it's written exactly the same as in the dropdown list as in the HAS function. Use the @cell argument to tell smartsheet to perform this operation on every cell of the preceding range.

Hope it helped!

Ameya Athalye

Hi @David Joyeuse,

Thanks for your quick reply. I tried your formula and it partially worked. It only worked in cases where "Business/ Content" was the only option selected. It ignored cases where more than one option were selected. The other thing I noticed, all the formulae I have tried so far only count the first selected value.

As i was reading up on "HAS" function usage, they said it'll return "true" only if the content matches exactly otherwise it returns "false". which means it'll discard all occurrences of multiple values.

The HAS function is designed to work with multi-select dropdown and multi-contact columns. It won’t cause an error if used with other column types, but it will only return “true” if the criterion exactly matches the contents of the specified cell.In the example above, if [Day of Week]1 is in a Text/Number column, HAS will return “true” if [Day of Week]1 = “Monday” but will return “false” if [Day of Week]1 = “Monday Tuesday.”

I'll keep trying. Thanks again for your help.

David Joyeuse

@ameyaa

The example given in the HAS function page is if the column being tested is a string text. It should be working fine on a dropdown list.

Another option to try would be the FIND function, where you can only check for a string of 3/4 characters that is unique to each value would do the trick too.

=COUNTIFS({Email}, Email@row, {committee selection}, FIND("ss/ C",@cell)>0)

Should return all Business/ Content items as long as there is not another item with the "ss/ C" string within it :)

If I remember correctly, items in multi dropdown list are separated by two spaces or CHAR(10).

Hope it helped!

Ameya Athalye

Thanks @David Joyeuse , let me try this.

Ameya Athalye

@David Joyeuse worked! Thank you so much!

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.