Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 22, 2020 02:02 PM
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!
Solved! Go to Solution.
May 22, 2020 03:34 PM
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.
May 22, 2020 03:34 PM
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.
Nov 09, 2023 08:30 AM
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,