Counting specific values in multi select cells


I've read through several different posts about which formula to use to count specific text values in a multi-select cell however, I'm still getting errors on my end. I'm trying to get a count for each virtual event my team has selected. The formulas I have tried are:

  1. =COUNTIF([Virtual Event]:[Virtual Event], CONTAINS("Margarita Making Class", [Virtual Event]:[Virtual Event])) - results return '0'
  2. =COUNTIF([Virtual Event]:[Virtual Event], CONTAINS("Margarita Making Class")) - result returns INVALID OPERATION
  3. =COUNTIF([Virtual Event]:[Virtual Event], HAS("Margarita Making Class")) - result returns INVALID OPERATION

Not sure what I'm doing wrong? Maybe formatting?


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @TJordan,

    HAS will work, but you need to make a slight change to add an @cell reference:

    =COUNTIF([Virtual Event]:[Virtual Event], HAS(@cell, "Margarita Making Class"))

    Hope this helps, but if you've any problems/questions, let us know!

  • TJordan
    TJordan ✭✭

    Hi @Nick Korna !

    Thanks for helping me on this. The formula I want to use needs to be a column reference vs. @row. I tried this and the results still came out as 0 or INVALID OPERATION.

    =COUNTIF([Virtual Event]:[Virtual Event], CONTAINS([Virtual Event]:[Virtual Event], "Margarita Making Class"))

    =COUNTIF([Virtual Event]:[Virtual Event], HAS([Virtual Event]:[Virtual Event], "Margarita Making Class"))

    Here's an example of the sheet.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    The @ cell is checking if the text appears in a given cell - the range is still the whole column.

    If you're wanting a check for the row (which will end up as a 1 if present/0 if not) then you would use:

    =COUNTIF([Virtual Event]@row, HAS(@cell, "Margarita Making Class"))

    You can do some extra things with this if necessary (e.g. folding it into an IF statement to give something other than a binary response), but I am not sure what your desired end outcome is.

  • TJordan
    TJordan ✭✭

    Gotcha! I'm hoping I can use this formula on the sheet summary to get a count for each of the virtual event options:

    -Office Feud (Have your team compete against the other ProServ teams)

    -Margarita Making Class

    -Office Mix and Mingle

    -Haunted House (Virtual Escape)

    -Do Good Dash (Online Charity- based team building)

    -Tiny Campfire (Ghost Stories & Smores)


    -Not Interested

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!