How to create formula in a multi-select column that will populate added value when multiple selected

Katherine Cline

Can someone help me? If I have a multi-selection column and want to have it calculate the total value if more than one is selected what would I need to change in this formula?

I need to provide values to the selections and if multiple selections are made I'd like to have it populate the total value. This column is group in to a final scoring column formula. I was trying to prevent having to add 4 more columns and then add those to the final score formula for 6 different sheets. Thanks!

=IF(LEFT([Business Critical]@row, 1) = "1", 50,

IF(LEFT([Business Critical]@row, 1) = "2", 25,

IF(LEFT([Business Critical]@row, 1) = "3", 30,

IF(LEFT([Business Critical]@row, 1) = "4", 25,

IF(LEFT([Business Critical]@row, 1) = "5", 50, 0)))))

Andrée Starå

Hi @Katherine Cline

I hope you're well and safe!

Try something like this.

=IF(CONTAINS(1, [Business Critical]@row), 50 + IF(CONTAINS(2, [Business Critical]@row), 25) + IF(CONTAINS(3, [Business Critical]@row), 30) + (IF(CONTAINS(4, [Business Critical]@row), 25) + IF(CONTAINS(5, [Business Critical]@row), 50)))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Katherine Cline

Hi Andree! Thanks! I tried it and I don't see any error but I also don't see the value populate for this example field I'm testing it on right now. For example selections of 2 & 4 would total to 50 and with that new formula it's just blank.

Andrée Starå

@Katherine Cline

There was a typo.

Try this.

=IF(CONTAINS(1, [Business Critical]@row), 50) + IF(CONTAINS(2, [Business Critical]@row), 25) + IF(CONTAINS(3, [Business Critical]@row), 30) + (IF(CONTAINS(4, [Business Critical]@row), 25) + IF(CONTAINS(5, [Business Critical]@row), 50))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Katherine Cline

I'm thinking I may need to try a statement like this:  IF (CONDITION) THEN (OUTPUT) ELSE(OUTPUT)

Katherine Cline

https://community.smartsheet.com/discussion/comment/340112#Comment_340112

Thank you! It worked! I also noticed the typo and had thought I corrected it. I'm a novice so thank you so much!

Andrée Starå

@Katherine Cline

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!