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?

[Gary_Ford]

Give this a look.

Airtable - Support

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

[lexx]

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

[JonathanBowen]

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

[Elias_Gomez_Sai]

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:

[JonathanBowen]

@Elias_Gomez_Sainz Ingenious! Like it.

[JonathanBowen]

And more generally applicable too.

[Elias_Gomez_Sai]

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

[Gary_Ford]

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

[lexx]

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…