2 or More Multiple Select Formula

[TaWyana_Jones]

Hello! I am trying to figure out a formula to help me calculate a score for a multiple select field. For example, I have 6 different issues to choose from. If they select “no issues” I want the score to be “20”, if they select 1 of the issues, I want the score to be “5”, and if they select 2 or more of the issues, I want the score to be “0”. Is there a way to do this with a multiple select field? Thank you in advance!

[kuovonne · Accepted solution]

Welcome to the Airtable community!

There is no direct formula to tell how many options are selected in a multiple select field, however, you can use a formula to calculate the number. Then you can use nested IF statements to convert the the number of options selected to your desired numeric value.

Note: this method will not work if any of the options include a comma.

IF( 
  {MultiSelectField} = BLANK(),
  20,
  IF(
    LEN({MultiSelectField} & "") - LEN(SUBSTITUTE({MultiSelectField}, ",", "")) = 0,
    5,
    0
  )
)

If this formula solves your problem, please mark this post as a solution. If it does not, please post a screen capture so that we can see if there is something special about your use case.

[Mike_Shaw1]

Can you explain what this part of your formula does exactly?  I'm trying to do something similar:

IF(
    LEN({MultiSelectField} & "") - LEN(SUBSTITUTE({MultiSelectField}, ",", "")) = 0,
    5,