Summing $ from Multi-select dropdown that includes text

Paul Reeves
Paul Reeves ✭✭✭✭✭
edited 03/31/23 in Formulas and Functions

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


  • Paul Reeves
    Paul Reeves ✭✭✭✭✭
    edited 03/31/23

    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?

  • Genevieve P.
    Genevieve P. Employee Admin
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!