Skip to main content
Solved

Grouping Multi-select using a formula

A

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.

Best answer by momentsgoneby80

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.

View original
Did this topic help you find an answer to your question?

6 replies

M
Forum|alt.badge.img+2

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.

A
1 person likes this
  • A
    Andrew_Davies
A
  • Amy_Kesling
  • Author
  • New Participant
  • 2 replies
  • May 9, 2022

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!

V
  • Vivid-Squid
  • Inspiring
  • 532 replies
  • May 9, 2022
Amy_Kesling wrote:

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.

M
Forum|alt.badge.img+2
Amy_Kesling wrote:

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!


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.

A
  • Amy_Kesling
  • Author
  • New Participant
  • 2 replies
  • May 10, 2022
Vivid-Squid wrote:

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.


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

G
  • GraceFNQ
  • New Participant
  • 1 reply
  • January 30, 2024

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 

Reply


Most helpful members this week

Terms & Conditions