Counting in a Multi-Select Cell While Having Additionally Criteria

Brittany Pair

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

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 : Bassam.k@mobilproject.it

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, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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!