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

[Adisa_Nicholson]

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.

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.

[Sho · Accepted solution]

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
)