Help

Grouping Multi-select using a formula

Topic Labels: Formulas
Solved
Jump to Solution
2374 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Amy_Kesling
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions
momentsgoneby80
7 - App Architect
7 - App Architect

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.

See Solution in Thread

6 Replies 6
momentsgoneby80
7 - App Architect
7 - App Architect

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
4 - Data Explorer
4 - Data Explorer

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!

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.
image

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.

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

GraceFNQ
4 - Data Explorer
4 - Data Explorer

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