Count of Selections within a Multi-Select Dropdown

01/18/21
Answered - Pending Review

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!

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    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.qCinda.q ✭✭✭✭✭

    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 CronkMark Cronk ✭✭✭✭✭

    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.qCinda.q ✭✭✭✭✭

    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.

Sign In or Register to comment.