Skip to main content

Hello! Do you guys know if it’s possible to create a formula that return a rate (1 to 3) IF:



  • in the field {name field}, there are both “option1” AND “option2” = 3

  • in the field {name field}, there is only “option1” = 2

  • in the field {name field}, there are no “option1” OR “option2” = 1


I tried to use IF, FIND, AND, OR but I couldn’t do it 😦


Thanks for support!

Hey @Edoardo_Abate!

Welcome in!


So, I presume you’re using a multi-select field.

If you are, then this formula format will work with the parameters you provided:


IF(

    {Field},

    IF(

        {Field} = "option1, option2",

        3,

        IF(

            {Field} = "option1",

            2

        )

    ),

    1

)

Truth be told, multi-select fields are an absolute pain to work with sometimes.

Here, Airtable treats the multi-select values as a string containing the comma-separated values.


Lemme know if you have any questions or run into any issues!

Hey @Edoardo_Abate!

Welcome in!


So, I presume you’re using a multi-select field.

If you are, then this formula format will work with the parameters you provided:


IF(

    {Field},

    IF(

        {Field} = "option1, option2",

        3,

        IF(

            {Field} = "option1",

            2

        )

    ),

    1

)

Truth be told, multi-select fields are an absolute pain to work with sometimes.

Here, Airtable treats the multi-select values as a string containing the comma-separated values.


Lemme know if you have any questions or run into any issues!


Thank you Ben!!

It works, I’v just modified like this:


IF(

  AND(

  FIND("option1",{field}),

  FIND("option2",{field})

  ),"5",

	IF(

	OR(

	FIND("option1",{field}),

	FIND("option2",{field})

	),"4",

    		IF(

      		AND(

       		FIND("option2",{field}), 

        	FIND("option3",{field})

        	),"3",

      			IF(

          		AND(

            		FIND("option3",{field}),

            		FIND("option4",{field})

          		), "1", "2"

      )

    )

  )

)

Thanks a lot 🙂

Thank you Ben!!

It works, I’v just modified like this:


IF(

  AND(

  FIND("option1",{field}),

  FIND("option2",{field})

  ),"5",

	IF(

	OR(

	FIND("option1",{field}),

	FIND("option2",{field})

	),"4",

    		IF(

      		AND(

       		FIND("option2",{field}), 

        	FIND("option3",{field})

        	),"3",

      			IF(

          		AND(

            		FIND("option3",{field}),

            		FIND("option4",{field})

          		), "1", "2"

      )

    )

  )

)

Thanks a lot 🙂


The OR(option1, option2) condition should be your last condition checked, or should at least be after the AND(option2, option 3) condition. Your formula as written will never check if both 2 and 3 are present since it stops running once 2 is found.

The OR(option1, option2) condition should be your last condition checked, or should at least be after the AND(option2, option 3) condition. Your formula as written will never check if both 2 and 3 are present since it stops running once 2 is found.


You’re right @Kamille_Parks!! I really appreciated your support guys, now everything works correctly :star_struck:

Thanks again

Hi,

Depending on your final goal, you can also use variations of:


SWITCH(

8*(FIND(“option1”,{field})>0)+

4*(FIND(“option2”,{field})>0)+

2*(FIND(“option3”,{field})>0)+

(FIND(“option4”,{field})>0),

0,‘empty cell’,

1,‘just option4’,

2,‘just option3’,

3,‘option 3 and 4 together’,

…

15,‘all are present’,

‘some default’

)

Reply


Terms & Conditions