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.****

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

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.

All comments

Paul Reeves

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.

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.

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.