Sum a Multi Select Dropdown
I'm looking for the formula I would need to use if I have a multi select dropdown with numbers and words. I would only be looking to sum up the numbers. Some of the cells have numbers and words in the same cell.
Any help would be appreciated!
Thanks,
Eva
Best Answer

I wasn't able to make it work without using at least one helper column.
If you want to leave the data entry in a single column and just pull the numbers into a separate column though, that formula would look like this:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Hours@row, CHAR(10), ""), "Live In", ""), "Mutual", ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

Are you able to provide a screenshot of the source data so we can see a sampling of what we are working with?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome Unfortunately, I can't share the original sheet due to company privacy, but this is how it would look. I would be looking to sum up the amount of hours the company is servicing based on the sum of hours per member. I do have options though of selecting live in or mutual with the amount of hours.

Are the hours random numbers or are they only able to select 5 or 10?

@ker9 They're at random. Each member is different. It starts at 1 and goes up to 168.

The quick answer is to separate the column into two columns  one for words and one for numbers.
How many different text options are there?

@ker9 Since there is only two dropdown options (word options) I was hoping to leave it as one column due to the sheet being pretty large and I try to avoid adding more columns if possible.
Obviously, if that's my only option, I'll work on that, but I was hoping some formula is available to avoid this.

If you only have the two text options of Live In and Mutual, you can put this in a separate column and then sum the new helper column. This should remove the text and return the number.
=IF(CONTAINS("Live In", Hours@row), SUBSTITUTE(Hours@row, "Live In", ""), IF(CONTAINS("Mutual", Hours@row), SUBSTITUTE(Hours@row, "Mutual", ""), Hours@row))

Unfortunately, it seems the formula leaves you with text instead of a number. I have not been able to convert it to a number in one formula. Until @Paul Newcome can help, you could add a 2nd helper column with this formula to convert it to a number, where "Helper" is the name of the column with the first formula above.
=VALUE(Helper@row)

By the time you add in the helper columns you need, it actually takes up less space to separate it into two columns. One for the text and one for the numbers.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome  I agree but OP was hoping for a formula.

@ker9 Yes. But to use a formula you would still need a helper column or quite possibly multiple helper columns.
I do have an idea that MIGHT work, but I will have to test it first.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

I wasn't able to make it work without using at least one helper column.
If you want to leave the data entry in a single column and just pull the numbers into a separate column though, that formula would look like this:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Hours@row, CHAR(10), ""), "Live In", ""), "Mutual", ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome  agree need at least one helper. I thought I could add "VALUE" into my formula but I could not get it to work, possibly because of the CHAR(10) that is in there? It appears Multiselect is always going to return text even if you have numbers as the selection options whereas Single select with numbers returns numbers.

Multiselect will always output text. That's why we use the VALUE function. Then we use the nested SUBSTITUTE functions to remove each of the text variables including the line break delimiter of CHAR(10).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome  BEAUTIFUL formula! Thank you for the lessons!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!