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

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

Screen Shot 2022-08-17 at 10.48.03 AM.png

Screen Shot 2022-08-17 at 10.47.45 AM.png

Find more posts tagged with

Accepted answers

All comments

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.

Screen Shot 2022-08-17 at 11.28.03 AM.png

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.