Grouping Multi-select using a formula

[Amy_Kesling]

I have a field where people can select multiple options. The question is “what are some emotions you experienced” and they can select from 9 options. I want to know how many people choose Joy and Anticipation, whether they’re the only options selected or if they’re part of a bunch of responses. I’ve found several ways to set up a formula field that tells me if someone ONLY chose Joy or Anticipation, but it doesn’t pick up if someone chooses Anticipation and Sadness or Joy and Anticipation together. There are too many possible combinations to realistically set up a nested IF formula.

Is there a way to write a formula that will tell me if someone chooses either option, regardless of whether they chose other options too?

I have tried OR, FIND, SEARCH, and REGEX, though it’s likely I’m just setting it up incorrectly!

If it helps, the data is coming in through an external site via Zapier, so if needed I can change the field type from multi-select to text.

[momentsgoneby80 · Accepted solution]

Hi and welcome @Amy_Kesling!
The following should acomplish what you’re after. I addapted it so it shows “ :white_check_mark: Joy / Anticipation” if either of those are found and “ :heavy_plus_sign: Joy / Anticipation” if those feelings + other feelings are found.

IF(
	AND(
		OR(
			FIND('Joy',{Multi-select Field Name}),
			FIND('Anticipation',{Multi-select Field Name})
		),
		OR(
			FIND('Sadness',{Multi-select Field Name}),
			FIND('Other emotion 1',{Multi-select Field Name}),
			FIND('Other emotion 2',{Multi-select Field Name}),
			FIND('Other emotion 3',{Multi-select Field Name}),
			FIND('Other emotion 4',{Multi-select Field Name}),
			FIND('Other emotion 5',{Multi-select Field Name}),
			FIND('Other emotion 6',{Multi-select Field Name}),
			FIND('Other emotion 7',{Multi-select Field Name})			
		)
	),
	'➕ Joy / Anticipation',
	IF(
		OR(
			FIND('Joy',{Multi-select Field Name}),
			FIND('Anticipation',{Multi-select Field Name})
		),
		'✅ Joy / Anticipation'
	)
)

Hope that helps.

[Amy_Kesling]

Thank you!! This works if I copy/paste exactly as you have it and sub in my field names and emotion terms…but for some reason when I try to add another term to the Joy/Anticipation list (I actually need Joy, Anticipation, and Trust in one category), it isn’t picking up Trust.

I also need a way to label the other group of responses (the other 6 options) that differentiates it from blanks - not everyone responds to this question and I’d like to be able to see answer “Of everyone who responded, how many people selected at least one of these 3 emotions?”

I don’t really need to know if they’re selecting just the 3 I’m looking for, it really can be with any combination of other responses.

Thank you so much!

[Vivid-Squid]

Hi @Amy_Kesling ,
Is there a reason you need a formula for this? Why not just use the native Airtable grouping feature. It will show you all of the options that have been selected.

[momentsgoneby80]

Try this

IF({Multi-select Field Name},
	IF(
		AND(
			OR(
				FIND('Joy',{Multi-select Field Name}),
				FIND('Anticipation',{Multi-select Field Name}),
				FIND('Trust',{Multi-select Field Name})
			),
			OR(
				FIND('Sadness',{Multi-select Field Name}),
				FIND('Other emotion 1',{Multi-select Field Name}),
				FIND('Other emotion 2',{Multi-select Field Name}),
				FIND('Other emotion 3',{Multi-select Field Name}),
				FIND('Other emotion 4',{Multi-select Field Name}),
				FIND('Other emotion 5',{Multi-select Field Name})		
			)
		),
		'➕ Joy / Anticipation / Trust',
		IF(
			OR(
				FIND('Joy',{Multi-select Field Name}),
				FIND('Anticipation',{Multi-select Field Name}),
				FIND('Trust',{Multi-select Field Name})
			),
			'✅ Joy / Anticipation / Trust',
			'☑ Other Emotions'
		)
	)
)

Edited to remove excess emotion options.

[Amy_Kesling]

Ultimately I’m creating a pivot table out of it.

[GraceFNQ]

Hi All,

This seems super close to what I am trying to do... but opposite.. We have students selecting their gymnastic program and then their level, they select multiple programs and levels. Is there a possibility to have a view with all programs and then each level grouped together instead of Airtable having numerous groups such as Level 1 & level 5, Level 9 & level 8... Just have all of 8 together, all of 9, 5, 1 etc? 

Thank you in advance