Skip to main content

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

  • April 3, 2017
  • 2 replies
  • 22 views
E
Forum|alt.badge.img+1
  • EAugum
  • Participating Frequently

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 replies

    C
    Forum|alt.badge.img+18

    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.

      E
      Forum|alt.badge.img+1
      • EAugum
      • Author
      • Participating Frequently
      • April 4, 2017

      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.


      Perfect result! Thank you so much, Chester!

      Terms & ConditionsAccessibility statement