Identifying specific combinations from a multi-select field
Good morning, fellow Smartsheeters.
I have a master roadmap sheet that we use to populate numerous dashboards and reports.
One of the columns on this roadmap is Project Type.
It is a multi select drop down with the following values:
Revenue Opportunity, Revenue Loss Avoidance, Risk Compliance & Cost Savings
The stakeholders stated that any projects that are soley Revenue Loss Avoidance or Risk Compliance should not appear on the dashboards or reports.
Simple enough, I identify them using the following formula to check a box. If the box is checked, the row is excluded from the dashboards/reports.
=IF(OR([Project Type]1 = "Risk Compliance", [Project Type]1 = "Revenue Loss Avoidance"), 1, 0)
I just noticed that some projects contain BOTH Risk Compliance and Revenue Loss Avoidance - which my above formula is failing to capture.
I'd like to add another condition to the formula to catch this instance, but haven't been able to identify multiple selections correctly.
I can't use 'contains' because if a project has, say, Risk Compliance and Revenue Opportunity it needs to appear on the dashboard.
Scratching my head here, so I appreciate any help.
Thanks a ton,
Help Article Resources
Check out the Formula Handbook template!