Formula to use across multiple columns?

[lexx]

I have 3 separate fields and I want the 4th field to show a result based on the 3.

The responses can be yes, no, maybe

If at least 2 of the 3 columns are YES, then result should be YES
If at least 2 of the 3 columns are NO, then result should be NO
If at least 2 of the 3 columns are MAYBE, then the result should be MAYBE
If each column has one response of each, then the result should be MAYBE

I am new to Airtable and am more familiar with Excel, so a simple formula or guidance would be helpful. My current Excel has a long formula of nested IF and COUNTIF. Are there equivalents of those on here?

[Elias_Gomez_Sai]

lexx:

it doesn’t seem to be accounting for the 6 different Yes, No, Maybe combinations

I totally missed your fourth rule :man_facepalming: , but completing the schema and adding it to the formula should be easy.

[Elias_Gomez_Sai · Accepted solution]

Doing this I realized that you just need to add a bunch of ‘Maybes’ in the third switches as the default option. Even you can simplify green sub-branches as ‘IFs’ because there are 2 ‘Maybes’.

Also, I noticed that I missed several commas because of the copy-pasting (now the @JonathanBowen’s solutions begins to feel better :smile_cat: ). Anyway, the important is to understand the method and the logic, and you could do it in other scenarios.

I leave the full formula, I think now it’s correct:

SWITCH(
	'Col1',
	'Y', SWITCH(
		'Col2',
		'N', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No',
			'Maybe'
		),
		'M', IF(
			'Col3' = 'Y',
			'Yes',
			'Maybe'
		),
		'Yes'
	),
	'N', SWITCH(
		'Col2',
		'Y', SWITCH(
			'Col3',
			'Y', 'Yes',
			'N', 'No',
			'Maybe'
		),
		'M', IF(
			'Col3' = 'N',
			'NO',
			'Maybe'
		),
		'No'
	),
	'M', SWITCH(
		'Col2',
		'Y', IF(
			'Col3' = 'Y',
			'Yes',
			'Maybe'
		),
		'N', IF(
			'Col3' = 'N',
			'No',
			'Maybe'
		),
		'Maybe'
	)
)

Also, what @Gary_Ford said is very true.

[lexx]

Perfect. Thank you so much! and here I was just only beginning to understand the switch formula haha, I need to make some time to sit down and just look at it to fully understand it. But this was a big help!

@JonathanBowen - thank you for your response as well, it made sense in its own way but more extra parts than needed.

@Gary_Ford thank you for the insight, Yes I am still getting myself learned on the differences as I have never used a database application before. I have only used Excel. I will probably be using airtable more as I do like some of the capabilities it provides, so I will definitely continue to read up on it.

Thank you again everyone!

[Elias_Gomez_Sai]

lexx:

beginning to understand the switch formula

It is like an IF, but with several checks, similar to a nested IFs chain. Let’s say you have an Status fields for some tasks, and you want to output an emoji depending on it, so the switch is:

And this is what the help says:

[Elias_Gomez_Sai]

Well, the default result should be an emoji to be consistent, so:

SWITCH(
	{Status},
	'Waiting', '⏳',
	'Working', '🔄',
	'Done', '✅',
	'❓'
)