Formula - Silo or Combo Items in a Multi-select List
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
-
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
-
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
-
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.)
-
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!
-
Hi Nick! This worked exactly as needed. Thank you so much!!!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!