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?



  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • NocturnalAnimal

    COUNTM would count all the labs in the cell. I need only the labs with moderate or high counted.

  • NocturnalAnimal
    NocturnalAnimal ✭✭✭
    edited 11/15/21

    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)

  • NocturnalAnimal

    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)

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

    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.

  • NocturnalAnimal

    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.

  • NocturnalAnimal

    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)

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @NocturnalAnimal

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!