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", ""))
Answers

Are you able to provide a screenshot of the source data so we can see a sampling of what we are working with?

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

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

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", ""))

@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).

@Paul Newcome  BEAUTIFUL formula! Thank you for the lessons!
Help Article Resources
Categories
Check out the Formula Handbook template!