IF-formula. If all fields = X, then Y


#1

Hi,

I have a table in a base with 12 fields, all Single selects with the same options in all fields.
In field 13 I would like to display a ”status” based on the various options selected in field 1-12.

As an example I belive an actual Airtable-table shows better what I want than me trying to explain it:
https://airtable.com/shrMPMihtqdfgseej/tblPIeT83np9akZ4n

My thinking was to use an IF-formula within an IF-formula within an IF-formula etc. etc., but I couldn´t really get that to work.
Any other way this could be solved better?

Thanks in advance!


#2

While not the most graceful solution, this should do it.

Create a new formula field called “Join” with this formula that concatenates all the status columns:
{1} & "," & {2} & "," & {3} & "," & {4} & "," & {5} & "," & {6} & "," & {7} & "," & {8} & "," & {9} & "," & {10} & "," & {11} & "," & {12}

Then create a new formula field called “Status” with this:

IF(
	FIND(",,",{Join}),
	"Missing Status",
	IF(
		FIND("W",{Join}),
		"Working",
		IF(
			FIND("Q",{Join}),
			"QA",
			"Done"
		)
	)
)

This is where I wish that Airtable allowed “creating” arrays. Then we could build an array from the values of the various columns and then use ARRAYUNIQUE and a few IF statements. But alas, all we’re left with is clunky solutions like this one.


Create a field/view to show records relevant to a date range
#3

Perfect result! Thank you so much, Chester!