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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!