Calculating Values

New to Smartsheet here.

Background on what I am trying to do. I would like to count the specific words that were selected from a multi check box and then multiply the count with the $ amount associated with that text.

Example:

User selects: Apple, Banana, Orange, these (3) selected options then get displayed together in Cell A1 on the destination sheet.

In the source sheet I have Apple = $1, Banana= $2 and Orange=$3.

What I would like the formula to do is count the words in cell A1 then multiply that by the associated $ amount and sum the total in B1.

In this example the total amount would equal $6.

Find more posts tagged with

Accepted answers

All comments

Darren Mullen

@DB393 What comes to my mind immediately is using some if statements and the HAS() function to check if a given selection is in the dropdown. If the value is there, then add the appropriate $ value.

You'd have to have an if and HAS() for each option you are looking for. Not sure how many you have?

DB393

The list is approximately 30. That could potentially be selected. I was hoping that I could combine a couple statements that would allow perform the appropriate calculation instead of a long tedious formula.

Almost like an index/match/count/sum formula.

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.