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

Topic Labels: Formulas
Solved
633 1
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
11 - Venus

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
)``````

11 - Venus

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
)``````