Identifying specific combinations from a multi-select field

Alan Rappa

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,

-Alan

Genevieve P.

Hi @Alan Rappa

The issue here is that you have a multi-select column, and your formula is only looking for one result. It will only look for if the cell has a single selection that equals exactly what you have "in these". You'll want to add in a HAS function, which will check to see if the cell HAS either of these criteria! (Even if both are selected, or one is selected with another option).

HAS was created specifically for multi-select columns. Try this:

=IF(OR(HAS([Project Type]@row, "Risk Compliance"), HAS([Project Type]@row, "Revenue Loss Avoidance")), 1, 0)

Cheers!

Genevieve

Alan Rappa

thanks so much @Genevieve P

I'll give this a shot, though would this work for all my criteria?

I need to be able to identify when Project type is Risk Compliance, Revnue Loss Avoidance, and Risk Compliance + Revenue Loss Avoidance only.

To clarify a little more:

If Revenue Loss Avoidance = Check

If Risk Compliance = Check

If Revenue Loss Avoidance + Risk Compliance = Check

If Revenue loss Avoidance + Cost Savings = Uncheck

If Risk Compliance + Revenue Opportunity = Uncheck

If I use HAS, would those last two scenaros be correct? or would they be checked because Project Type has Risk Compliance or Revenue Loss Avoidance?

Thanks so much

Genevieve P.

Hi @Alan Rappa,

My apologies, I misunderstood the potential options. Your last two scenarios would also be checked because they contain one of the possible options. To fix this, instead of using HAS for each individual criteria then, you can keep your original formula as-is (looking for if one of those values appears on its own), and then add a third criteria using AND and HAS together, looking for when those two options appear together.

Try this:

=IF(OR([Project Type]@row = "Risk Compliance", [Project Type]@row = "Revenue Loss Avoidance", AND(HAS([Project Type]@row, "Risk Compliance"), HAS([Project Type]@row, "Revenue Loss Avoidance"))), 1, 0)

Now, keep in mind that if these two selections were both in the cell with a third option, this would check:

If Revenue Loss Avoidance = Check

If Risk Compliance = Check

If Revenue Loss Avoidance + Risk Compliance = Check

If Revenue loss Avoidance + Cost Savings = Uncheck

If Risk Compliance + Revenue Opportunity = Uncheck

If Risk Compliance + Revenue Opportunity + Revenue Loss Avoidance = Check

Test this out and let me know if it works for you or if you have any other questions!

Cheers,

Genevieve

Alan Rappa

Thanks @Genevieve P

Certainly getting closer (and I need to get in the habit of using @row - it just seems fussy for me).

Now if there was a way I could exclude that last scenario you mentioned I'd be in business :)

Thanks so much!

Alan Rappa

I wonder if I can do this backwards...

meaning, if Project type has Revenue Opportunity or Cost savings then don't check, else Check

What do you think?

edit: This actually works, EXCEPT for one unforseen scenario

The box is checked when PROJECT TYPE is blank.... ugghhhhhh

Trying to figure out how to get that out of the equation as well :(

Genevieve P.

Hmm, that's actually a really good point. We could do the opposite! You only have the 4 options, is that correct? (Revenue Opportunity, Revenue Loss Avoidance, Risk Compliance, Cost Savings)

If so, we can just say that if that cell has either Risk Compliance OR Cost Savings, don't check the box. Otherwise, check! This will then apply to all of your scenarios above... whether there's one or two of the other options selected.

Try this:

=IF(OR(HAS([Project Type]@row, "Revenue Opportunity"), HAS([Project Type]@row, "Cost Savings")), 0, 1)

Great idea!

Paul Newcome

Slight edit to not check boxes where the [Project Type] is blank...

=IF(OR([Project Type]@row = "", HAS([Project Type]@row, "Revenue Opportunity"), HAS([Project Type]@row, "Cost Savings")), 0, 1)

Genevieve P.

@Paul Newcome Brilliant, thank you!!

@Alan Rappa, use Paul's one instead ^^

Paul Newcome

No worries. I just happened to notice the comment about there being a problem with blank rows being checked. I see that it is an edit, so it may have been posted after your solution.

Alan Rappa

thank you @Genevieve P & @Paul Newcome !

I was gunning for this:

=IF(OR([Project Type]@row = "", HAS([Project Type]@row, "Revenue Opportunity"), HAS([Project Type]@row, "Cost Savings")), 0, 1)

But I was messing up the approach.

I ended up with

=IF(OR(HAS([Project Type]@row, "Cost Saving"), HAS([Project Type]@row, "Revenue Opportunity"), ISBLANK([Project Type]@row)), 0, 1)

Which works as far as I can tell.

wondering why it didn't like it when I used [Project Type]@row = "" originally?

Thanks again for the help with this. It was throwing my dashboard numbers completely off :)

I owe you both!

Paul Newcome

Happy to help. 👍️

Genevieve P.

Glad you figured it out in the end!! 🙂