Currently having trouble counting data in a multiselect cell.

NocturnalAnimal

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

Hi @NocturnalAnimal ,

Try COUNTM

https://help.smartsheet.com/function/countm

NocturnalAnimal

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

NocturnalAnimal

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

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.

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