Summing $ from Multi-select dropdown that includes text
I am running into a problem that I have not been able to find in the community.
I have a multi-select dropdown column that includes text and $ values, e.g. A - $25.00, B - $30.00, etc... I would like to take the selection from the column and sum the $ values. I would then multiply that by the Qty, =( )* Qty@row.
I am able to calculate when one item is selected using =VALUE(SUBSTITUTE([Sample Products]@row, LEFT([Sample Products]@row,FIND("-", [Sample Products]@row)+1),"")) * Qty@row. However, when I have multiple selections it generates an INVALID VALUE error.
Any suggestions?
**** The highlighted columns are me thinking that if I can isolate the Sample Product selection I could then sum from those. However, I am not using them in this formula.****
paul e. reeves
Principal Business Analyst
HMH
Best Answer
-
Hi @Paul Reeves
The formula from Paul is what I would suggest. Since you're using a multi-select cell, you'll ned to search in the cell for each individual selection to then multiply it by the correct column amount.
Answers
-
Finding a similar solution from @Paul Newcome I used the following... however I would love to have something a little more dynamic.
=(IF(CONTAINS("A", [Sample Products]@row), A@row) + IF(CONTAINS("B", [Sample Products]@row), B@row) + IF(CONTAINS("C", [Sample Products]@row), C@row) + IF(CONTAINS("D", [Sample Products]@row), D@row) + IF(CONTAINS("E", [Sample Products]@row), E@row)) * Qty@row
Any other suggestions?
paul e. reeves
Principal Business Analyst
HMH
-
Hi @Paul Reeves
The formula from Paul is what I would suggest. Since you're using a multi-select cell, you'll ned to search in the cell for each individual selection to then multiply it by the correct column amount.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!