Simple count if from multi-select drop down

I am trying to create a function/formula to count all of the times a dropdown selection like "Apple" is used in a dropdown (multi select) column but nothing works.

User can select multiple items in the same cell.

Goal is to create a summary table that includes each of the dropdown options but I am having trouble figuring out how to format the function/formula.


Here is what I already tried that doesn't work:

=COUNTM({Spreadsheet Range 9}, Category9)

==> Category 9 being Cell9 in my current metric spreadsheet

=COUNTIFS({Spreadsheet Range 9}, (HAS(@cell, Category10)))

=COUNTIFS({Spreadsheet Range 9}, (CONTAINS("Corporate Services", @cell)))


Nothing works, do you have any idea why ?


@Paul Newcome , I saw you give a lot of help, any idea?

Thanks in advance!!

Answers

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭

    I don't know if this might help but I create multi select formulas but reference the row in my metrics sheet. For example:

    =COUNTIF({Request - Channel}, CONTAINS(Channel@row, @cell))

    {Request - Channel} is another sheet but then I reference the row instead of typing out something in quotes.

    So if its possible try making a column labeled Services and make Corporate Services the first row then your formula could look like this.

    =COUNTIF({Spreadsheet Range 9}, CONTAINS(Services@row, @cell))

    Hope this helps


    Side note: I recommend naming those Spreadsheet ranges. 😉

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The one with the HAS function should be working (I removed an unneeded set of parenthesis but everything else is exactly the same).

    =COUNTIFS({Spreadsheet Range 9}, HAS(@cell, Category10))


    Are you able to provide a screenshot of the source data and metrics sheet so we can compare the two?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!