Using HAS/CONTAINS functions with multiple choices

Hi there!

This is my first time posting a question so sorry if I end up doing this wrong!!

We run events across the UK and I have a sheet set up for each event site capturing data about the events, from day/date to type of event and breakdown of the number of people who attended.

We've now started capturing any 'additional activities/enhancements' that take place beyond our regular offering. To do this, I ask users to multi-select (from a drop down menu of 10) the 'extra' activities that took place. E.g. Arts & Crafts, Character Meet & Greet etc.

I've been asked to calculate how many 'Total Extra Experiences' we have delivered at Events, but the tricky thing is that each of the options in the multi-select carries a different value. For example:

3 of the options wouldn't be counted as an Experience
3 of the options would need to include the total patients who attended AND the total child guests who attended in the 'Experience value'
4 of the options would need to include the total number of attendees as the 'Experience value'

I was going to create two COUNTIFS helper columns, one that counts when any choices that need to include total patients + child guests are selected, and another that counts when any choices that need to include total attendees are selected. But I am stumped by the formula - can I have some help with it? I started with:

=COUNTIF([Additional Experiences & Enhancements]@row , HAS([Additional Experiences & Enhancements]@row , "Arts & Crafts", OR(HAS([Additional Experiences & Enhancements]@row, "Entertainer (e.g. Balloons, Magician"))))

But this is returning a value of 0 - perhaps I need to add an 'AND' in there somewhere?

Then I was planning to add more helper columns, maybe using the IF function, saying that, if there is a value in one of the helper columns above, then multiply by the relevant attendee data. This is making my head spin though - if there's an easier way to do this, please help!!

Thank you so much in advance - and apologies for the essay!!

Find more posts tagged with

Accepted answers

KPH

Hi @Beth Keane

As I understand it, a cell could include up to 10 events, and they fall into 3 categories:

Group 1 ignore (in my example these are events 1-3)
Group 2 multiply by 2 other columns (in my example these are events 4-6)
Group 3 multiply by 1 other column (in my example these are events 7-10)

If a cell has 2 events (say event 9 and event 10) and you use a COUNTIF with an OR, that formula will return 1. The cell contains either event 9 or event 10.

If you want this to return 2 (the total number of events in the group), then you need to count each separately and add them together.

Here column 5 shows the result of the formula in column 6.

So, to count the four events in Group 3, the formula would look like this (with "event n" replaced with the value in your drop down).

=SUM(COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 10")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 9")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 8")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 7")))

You would then multiply this by the number of attendees to get the value.

That formula would then be:

=(SUM(COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 10")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 9")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 8")), COUNTIF([Additional Experiences & Enhancement]@row , HAS(@cell , "Event 7")))) * Attendees@row

You can then repeat this for group 2, multiplying that total by the two columns.

I hope this is helpful.

All comments

Paul Newcome

Both HAS functions need to go inside of the OR function.

OR(HAS(…………), HAS(………))

KPH

Hi @Beth Keane

As I understand it, a cell could include up to 10 events, and they fall into 3 categories:

Group 1 ignore (in my example these are events 1-3)
Group 2 multiply by 2 other columns (in my example these are events 4-6)
Group 3 multiply by 1 other column (in my example these are events 7-10)

If a cell has 2 events (say event 9 and event 10) and you use a COUNTIF with an OR, that formula will return 1. The cell contains either event 9 or event 10.

If you want this to return 2 (the total number of events in the group), then you need to count each separately and add them together.

Here column 5 shows the result of the formula in column 6.

So, to count the four events in Group 3, the formula would look like this (with "event n" replaced with the value in your drop down).

You would then multiply this by the number of attendees to get the value.

That formula would then be:

You can then repeat this for group 2, multiplying that total by the two columns.

I hope this is helpful.

Beth Keane

Paul and KPH - you are my heros, thank you so much. I'm going to put my headphones on and try and tackle it today!! Thank you again.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.