Currently having trouble counting data in a multiselect cell.
Here is my currently formula, and it works to count if there is one lab with a value, but I need it to be able to count if there are multiple labs with Moderate in the cell. For example, I could have XXX-Moderate, AAA-Moderate, BBB-Waived, CCC-High. Right now the formula would only return 2 when it would be 3.
=COUNTIF([Lab Name]@row, CONTAINS("Moderate", @cell)) + COUNTIF([Lab Name]@row, CONTAINS("High", @cell))
What do I need to add?
Answers
-
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
COUNTM would count all the labs in the cell. I need only the labs with moderate or high counted.
-
I've tried different things like this but they all state #Unparseable or don't count all the values with "moderate"
so the closest I have got is with the Countif.
=COUNTM(IF([Lab Name]@row >= "Moderate", 1))
=COUNTM(IF(CONTAINS("Moderate", @cell),1))
I have also tried: But the same issue where it is not containing more than one moderate in the cell.
=COUNTIF([Lab Name]@row, FIND("Moderate", @cell) > 0)
-
What about something like this? this formula doesnt work but they all end in "- Moderate" or "- High" "- Waived"
=COUNTM(IF(RIGHT(@cell, 8)= "Moderate", 1) [Lab Name]@row)
-
An easy way to do this is just make a helper column. In that helper column write a formula
=if(contains("moderate",[Lab Name]@row),1,if(contains("high",[Lab Name]@row),1,0)
this will return a 1 for each entry that is moderate or high and a 0 for waived. then you could just do a count in the helper column.
-
I am all for helper columns! I tried the formula and it still is only returning 1 if there is more than 1 Moderate in the cell.
-
I've created a column in a different sheet that has all the values that I want to count. So essentially i just need a formula that if any of these values in the multiselect column are in this other sheet to count it as one. I have tried Has and Contains and they both are not counting right still. :(
=IF(HAS({Copy of Mod/High Range 1}, [Lab Name]@row), 1, 0)
-
CONTAINS will see a cell with XXX-Moderate and AAA-Moderate as "containing the word Moderate" which is why it counts 1.
Instead you'll want to use HAS, but HAS for each individual value, like so:
=COUNTIF([Lab Name]@row, HAS(@cell, "XXX-Moderate")) + COUNTIF([Lab Name]@row, HAS(@cell, "AAA-Moderate"))
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!