Help

Formulas for numerical scores based on 2 multi-select fields, each adding a different number

Topic Labels: Formulas
Solved
Jump to Solution
320 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Adisa_Nicholson
4 - Data Explorer
4 - Data Explorer

How do I have a formula based on 2 different multi-select fields, that will add a specified number to the resulting formula, based on how many options were selected for each multi-select field?

Remember that each multi-select will be adding on different number.

airtable base for technical support.png

Here is my formula so far.

 

 

 

(IF(
   {Pull Factors Attract},
   LEN({Pull Factors Attract}) - 
   LEN(
      SUBSTITUTE(
         {Pull Factors Attract},
         ",",
         ""
      ) 
   )  
)  + 0 ) * 2 

+ 

(IF(
   {Bonus Pull Factors},
   LEN({Bonus Pull Factors}) - 
   LEN(
      SUBSTITUTE(
         {Bonus Pull Factors},
         ",",
         ""
      ) 
   )  +0
)  + 2 ) * 1

 

 

 

You can see an example base here.

 

Here is the scores I would like added for each option chosen

  • Pull Factors Attract: 2
  • Bonus Pull Factors: 1

However it's not quite working with records with an empty field.

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

This ought to work.

IF(
   {Pull Factors Attract},
   LEN({Pull Factors Attract}) - 
   LEN(
      SUBSTITUTE(
         {Pull Factors Attract},
         ",",
         ""
      ) 
   ) + 1
) 
+ 
IF(
   {Bonus Pull Factors},
   (
      LEN({Bonus Pull Factors}) - 
      LEN(
         SUBSTITUTE(
            {Bonus Pull Factors},
            ",",
            ""
         ) 
      )
   ) * 2 + 2
)

 

See Solution in Thread

1 Reply 1
Sho
11 - Venus
11 - Venus

This ought to work.

IF(
   {Pull Factors Attract},
   LEN({Pull Factors Attract}) - 
   LEN(
      SUBSTITUTE(
         {Pull Factors Attract},
         ",",
         ""
      ) 
   ) + 1
) 
+ 
IF(
   {Bonus Pull Factors},
   (
      LEN({Bonus Pull Factors}) - 
      LEN(
         SUBSTITUTE(
            {Bonus Pull Factors},
            ",",
            ""
         ) 
      )
   ) * 2 + 2
)