Counting in a Multi-Select Cell While Having Additionally Criteria


Hello -

I am having issues figuring out the correct formula to use when counting criteria in a multi-select cell while also capturing a specific criteria. Please see below my example.

The sheet I have below is derived from a Form. Within that form, individuals who submitted a response had the option to select a Sport with a checkbox. When the user selected a check box in the form, a drop down menu would appear for them to select a variety of different 'Support Areas' for that Sport. Their responses are collected in the cells titled "Support Areas XYZ". I need an equation that not only counts how many 'Support Areas' for each Sport they selected, but also which Region they identified coming from (Captured in Column titled 'Region'.

I am planning on using this equation on a separate sheet so it does not interfere with the original responses from the Form, but if I can get any assistance on the type of equation I should be using to capture this information, I would greatly appreciate it.

Would also be happy to further elaborate if needed!

Thank you!


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 10/10/21

    Hi @Brittany Pair 

    Hope you are fine, you can use Count(collect( ...., with HAS Or Contains function ) to do that. please check my post

    if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

    My Email for sharing :

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Brittany Pair

    I hope you're well and safe!

    Not sure I follow.

    Do you want to get the total by a submitter or a total for all?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,

    I hope that helps!

    Be safe and have a fantastic weekend!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!