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)))))
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I'm thinking I may need to try a statement like this: IF (CONDITION) THEN (OUTPUT) ELSE(OUTPUT)
-
Thank you! It worked! I also noticed the typo and had thought I corrected it. I'm a novice so thank you so much!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!