Count of Selections within a Multi-Select Dropdown

Cinda.q · 01/18/21

Hey there!

I am looking for assistance in counting the values within a cell that is formatted as a multi-select dropdown for each date associated with the creation of the Smartsheet line. The creation date of the line is being populated from the system-generated column option so I have pulled out the month and date into separate identifying columns and not sure if this is my issue or not. Here is the formula that I have tried:

Please let me know what suggestion you have or if you need additional data to assist.

Thank you!

Mark Cronk · 01/19/21

Hi @Cinda.q ,

Try:

COUNTIF({Issue},AND(HAS({Month Created},@cell=12), HAS({Date Created}, @cell=[Month/ Date]@row)))

Assume that both {Month Created} and {Date Created} are multi-select drop downs. If they aren't, remove the HAS.

Work?

Mark

Cinda.q · 01/19/21

Mark-

This formula did not work :( I did remove the HAS because the only column notated in the formula with a multi-select drop down is the "Issue" column- which is why I was starting the the COUNTM formula originally.

I tried:

COUNTIF({Issue},AND({Month Created},@cell=12), {Date Created}, @cell=[Month/ Date]@row)) -- this resulted in #INVALID OPERATION

when I tired:

COUNTIF({Issue},AND(HAS({Month Created},@cell=12), HAS({Date Created}, @cell=[Month/ Date]@row))) -- this resulted in 0 value where I know there should be some sort of count output

I also tried:

=COUNTIF({Issue}, AND({Month Created} = 12, {Date Created} = 1) -- this also resulted in #INVALID OPERATION

Any other suggestions?

Mark Cronk · 01/19/21

HI @Cinda.q ,

I have a solution but it requires 2 helper columns and would work best as a separate metric sheet. Or, it may give you an idea so you can find a better solution.

Insert a multi-select column [Dec_Values] with the column formula: =JOIN(COLLECT({issue], {month created}, @cell=12, {date created}, @cell= [month/ date]@row), CHAR(10)))

This column places all of the drop down values that met the criteria into a single drop down cell.

On the same sheet great a text/number column [Dec_Cnt] with the formula = COUNTM([dec_values]@row)

This formula counts how many drop down values are in the cell you consolidated to.

Help at all?

Mark

Cinda.q · 01/19/21

Thanks for the assistance I was hoping to create a formula in a separate metric sheet to calculate this, but I ended up just adding a helper column that contained the COUNTM and then did a SUMIFS off of that column.

Count of Selections within a Multi-Select Dropdown

Answers

Help Article Resources

Categories