A Contains function to find at least one option from a multi select column

Matthew Emrich

I am using this formula

=COUNTIF({Activities}, CONTAINS(Activity@row, @cell))

to count if a column in another sheet has a specific activity. Both columns are multi-select and if there's a single activity in the Activity@row, it works fine, but when I ad a second activity to the row, it no longer counts it because the other column doesn't contain both activities.

Sheet with formula in BOP column

Second sheet with lookup column

even though the second sheet has both values, it doesn't register it.

Is there a way to modify the formula so it searches for both values and if either of them is present it marks it?

Paul Newcome

It looks like the sheet isn't outputting multiple choices but a single string. If multiple choices are selected in the same cell then it should look more like the second row in the first screenshot.

Matthew Emrich

That makes sense, the sheet I'm looking at has grouped everything together and do not have them as separate entries.

I have made the adjustments in the second sheet, but still do not get the output I want if there's something that is missing from the lookup column

Lookup column

sheet with formula

In the second line I have added "Feed" which is not on the lookup column cell. when I do this, it no longer recognizes that the column contains values in common. I'm looking to get the box checked if any of the values from one cell matches any values from the second cell. Is this Possible?

@Paul Newcome

Paul Newcome

Try something like this...

=IF(COUNTIFS({Other Sheet Lookup Column}, HAS(@cell, [Formula Sheet Multi Column]@row))> 0, 1)

Matthew Emrich

With the formula you suggested, it still doesn't register if there's something on the formula column that is not on the lookup column

Paul Newcome

I'm sorry, but I am not sure I understand your last comment.

Matthew Emrich

With the formula you provided, it is still not giving me a checkmark if the [Formula Sheet Multi Column]@row has a value that is not in the {Other Sheet Lookup Column}

[Formula Sheet Multi Column]

The second row is not getting a checkmark because it has the select Feed which is not in the source sheet, but all the other values are.

{Other Sheet Lookup Column}

I need to have it so that if any values match between the 2 cells I get a checkmark, even if none of the other values are present

Paul Newcome

How many possible selections could you have in a single cell on the formula sheet?

Matthew Emrich

it's a list of around 40 selections that can be in any combination