Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

2 or More Multiple Select Formula

Topic Labels: Formulas
Solved
Jump to Solution
1893 2
cancel
Showing results for 
Search instead for 
Did you mean: 
TaWyana_Jones
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

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,