Formula to use across multiple columns?

Give this a look.

Nested IF formulas

Basics of the IF function The IF function will return one of two values depending on whether a logical argument is true or not. Written in the format: IF([logical argument], [value1], [value2]) thi...

Give this a look.

Nested IF formulas

Basics of the IF function The IF function will return one of two values depending on whether a logical argument is true or not. Written in the format: IF([logical argument], [value1], [value2]) thi...

Thank you for your response, but I have already looked at that and it doesn’t achieve what I need to do. It doesn’t seem like Airtable does cell ranges like Excel.

This is my current Excel Formula for reference of what I need to translate into Airtable formulas.

=IF(COUNTIF(C2:E2,“Yes”)=3,“YES”,IF(COUNTIF(C2:E2,“Yes”)=2,“YES”,IF(COUNTIF(C2:E2,“Maybe”)=3,“MAYBE”,IF(COUNTIF(C2:E2,“Maybe”)=2,“MAYBE”,IF(COUNTIF(C2:E2,“No”)=3,“NO”,IF(COUNTIF(C2:E2,“No”)=2,“NO”,“MAYBE”))))))

Hi @lexx - Airtable doesn’t have a COUNTIF function, so you can’t replicate this directly (would be great if it did!). I tried various ways to solve this problem using FIND(), MID() etc, but all horrible or didn’t work. The best I could come up with is a total hack and a bit flukey:

Ignoring the order of the answers I think you’ve got 10 possible combinations of answers:

3 Yes
3 No,
3 Maybe? (ignore the “?” for now, will explain)
2 Yes, 1 No
etc, etc

If you join these 3 columns together and then find the length of each concatenated string you get:

By chance, the lengths are different for all combinations:

So you could now do a SWITCH() function on the length to determine the answer you require:

SWITCH(
Length,
6, "No",
7, "No",
8, "Yes",
.
.
.
18, "Maybe?"
)

And this is why you need the “?” at the end of the “Maybe”. If you don’t have this, you don’t get a unique value for each combo, so the hack doesn’t work.

Obviously, this isn’t a general solution to the problem, but a very specific one (and even then, only if you can tolerate “Maybe?” instead of “Maybe”. Or “Maybe!” or “May-be”. All of them terrible, TBH :slightly_smiling_face: ).

JB

SWITCH(
Length,
6, "No",
7, "No",
8, "Yes",
.
.
.
18, "Maybe?"
)

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

I’ve used capital letters for values and full words for results. You can change the values and it will work always. This is the schema I’ve followed:

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

@Elias_Gomez_Sainz Ingenious! Like it.

And more generally applicable too.

You were ingenious! :grinning_face_with_big_eyes: But I think the “logic approach” is more solid.

Keep in mind that AirTable is not a spreadsheet it is a relational database.
Your spreadsheet mind-set is fighting you with bad information working with relational databases. Spreadsheets and relational databases are not the same animal.

In AirTable each row is a record. For example in a Contact Database each row would be a different contact and the columns would hold the contact information. Records are composed of fields(columns). Fields or ( columns) are where the data lives. Each column is set up to do only one thing. It can hold data such as a contact first name. A column can also have a formula and the formula will act on every row for that column. What happens in columns (format, formulas, whatever) is consistent for every record. You can not have a custom calculation in column(field) 3 of row(record) 6 and a different calculation in column 3 row 7. Whatever you set up a column to do is what the columns does for every record (row) in the database. This does not mean that you can not achieve the same results you can with a spread sheet, but the way you are going to organize the data is completely different. You will never get there thinking in spreadsheet mind-set. This is a completely different tool. Read the airtable section that describes relational databases. Then look at the examples. You have to understand how relational databases work so that you can understand how you need set up your data in various related databases in order to setup a structure of related data that will then let you solve your particular problem. Relational Databases are not for every problem.

Gary

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

Thanks! This is great, but it doesn’t seem to be accounting for the 6 different Yes, No, Maybe combinations. Unless I’m doing something wrong…still taking a try at figuring all this out…

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

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'
	)
)

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!

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:

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

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