A Contains function to find at least one option from a multi select column
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?
Answers
-
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.
-
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?
-
Try something like this...
=IF(COUNTIFS({Other Sheet Lookup Column}, HAS(@cell, [Formula Sheet Multi Column]@row))> 0, 1)
-
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
-
I'm sorry, but I am not sure I understand your last comment.
-
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
-
How many possible selections could you have in a single cell on the formula sheet?
-
it's a list of around 40 selections that can be in any combination
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!