Identifying specific combinations from a multi-select field

Alan Rappa
Alan Rappa ✭✭✭✭
edited 07/29/20 in Formulas and Functions

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

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin
    edited 07/30/20

    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
    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
    Alan Rappa ✭✭✭✭
    edited 07/30/20

    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    @Paul Newcome Brilliant, thank you!!

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

  • Paul Newcome
    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
    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!

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad you figured it out in the end!! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!