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

Seatora

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

Nick Korna

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!

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)))

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

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

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

Seatora

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")