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.****
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?
-
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
Check out the Formula Handbook template!