Formula - Silo or Combo Items in a Multi-select List

Seatora
Seatora ✭✭✭✭
edited 04/12/24 in Formulas and Functions

Hi All,

I'm trying to create formula that can (within 1 multi-select column) count a specific value or any combination of a set of values. (We can use the list below for the sake of discussion, and I believe there are more than 30 possible options.) These may also appear in any order within each cell. I will inevitable have to add more colors to the list later on, so the solution must be "adjustable" to accommodate the changes. After doing a little bit of digging, I have found a few complicated (above my level) ways of achieving this, but I'm wondering if there isn't better or simpler way that I'm overlooking. Any ideas? Thanks in advance for ANY advice!!

Red, Green, Blue, Orange, Purple

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Seatora

    The brackets will vanish as the column name is a single word, and the brackets are only applicable if it's more than one.

    If you want a count where only "Red" is present (and no other values at all), then you can use something like this:

    =COUNTIFS(Colors:Colors, AND(HAS(@cell, "Red"), COUNTM(@cell) = 1))

    Example:

    If you want additional items, but where only those values are present, but no others then add the extras into the AND portion of the statement and increase the COUNTM to the relevant total.

    E.g. for "Red" & "Blue" together:

    =COUNTIFS(Colors:Colors, AND(HAS(@cell, "Red"), HAS(@cell, "Blue"), COUNTM(@cell) = 2))

    If you don't care about how many elements are present (in the above screenshot you wanted the Red Blue Green value included in the Red & Blue count), then just remove the COUNTM portion@

    =COUNTIFS(Colors:Colors, AND(HAS(@cell, "Red"), HAS(@cell, "Blue")))

    This would change the result from 1 to 2.

    If you have a lot of combinations this could take a while to do them all, though the order they appear in shouldn't matter.

    Hope this helps, but if you've any further problems/questions then just let us know!

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi @Seatora


    You will need to write a formula for each value/combination. If you're trying to get a count of rows which have Red, your formula would be =COUNTIF([Column containing the value]:[Column containing the value], CONTAINS("Red", @cell))


    If you're trying to get a count of rows where both Red and Blue appear, your formula would be =COUNTIF([Column containing the value]:[Column containing the value], AND(CONTAINS("Red", @cell), CONTAINS("Blue", @cell)))

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • Seatora
    Seatora ✭✭✭✭

    Thanks so much for your reply!!! This does count the cells that "red" appears in (4). However, I would like to get a count of cells that have "red" only (1). Then I would need a count of each singular or combination of options. Also, it would have to recognize these combinations in any order. (Other collaborators will be selecting these, and they will change over time.) Do you have any input on how this can be achieved? I'm open to options, such as pulling this over into a separate metrics sheet instead of using the sheet summary. I just can't seem to get the formula piece worked out. (P.S. When I paste in the formula as you provided, the brackets around "colors" disappear, which is curious.)


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Seatora

    The brackets will vanish as the column name is a single word, and the brackets are only applicable if it's more than one.

    If you want a count where only "Red" is present (and no other values at all), then you can use something like this:

    =COUNTIFS(Colors:Colors, AND(HAS(@cell, "Red"), COUNTM(@cell) = 1))

    Example:

    If you want additional items, but where only those values are present, but no others then add the extras into the AND portion of the statement and increase the COUNTM to the relevant total.

    E.g. for "Red" & "Blue" together:

    =COUNTIFS(Colors:Colors, AND(HAS(@cell, "Red"), HAS(@cell, "Blue"), COUNTM(@cell) = 2))

    If you don't care about how many elements are present (in the above screenshot you wanted the Red Blue Green value included in the Red & Blue count), then just remove the COUNTM portion@

    =COUNTIFS(Colors:Colors, AND(HAS(@cell, "Red"), HAS(@cell, "Blue")))

    This would change the result from 1 to 2.

    If you have a lot of combinations this could take a while to do them all, though the order they appear in shouldn't matter.

    Hope this helps, but if you've any further problems/questions then just let us know!

  • Seatora
    Seatora ✭✭✭✭

    Hi Nick! This worked exactly as needed. Thank you so much!!!

  • Seatora
    Seatora ✭✭✭✭
    edited 04/11/24

    Hi Nick, I need to modify these formulas a bit. Per your suggestions - I have written (15) formulas to accomodate all of the possible options (which are working well). However, now I need 2 versions of this set of formulas. As you are seeing, this has already gotten pretty complicated (which I'm ok with). However, if there is a simpler way to accomplish this, I'm open to suggestions. Are you still able to help??


    Version1: I need to see this for only projects that are noted as a "1_Tier 1: Priority Project"

    Column Name: Priority Type/Level

    Value must equal: 1_Tier 1: Priority Project


    Formula I was previously using for the option of "Other":


    =COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project")



    Version 2: I need to see this for only projects that are noted as one of the 7 values below

    Column Name: Priority Type/Level


    Value must equal one of the following:

    1_Tier 1: Priority Project

    2_Tier 2: Priority Project

    3_Tier 3: Unprioritized

    6_New Project Request

    7_Parked

    8_Cancelled

    9_Completed


    Formula I was previously using for the option of "Other":


    =COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project") + COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "2_Tier 2: Priority Project") + COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "3_Tier 3: Unprioritized") + COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "6_New Project Request") + COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "7_Parked") + COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "9_Completed") + COUNTIFS(Platform:Platform, "Other", [Priority Type/Level]:[Priority Type/Level], "8_Cancelled")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!