Skip to main content
Solved

2 or More Multiple Select Formula

  • May 22, 2020
  • 2 replies
  • 26 views
T

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!

Best answer by kuovonne

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.

2 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • May 22, 2020

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.

T
Justin_Barrett
Kamille_Parks11
TheMortgageMeister
Forum|alt.badge.img+11

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.


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,

 

Terms & ConditionsAccessibility statement