Sum a Multi Select Dropdown

Eva

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

Paul Newcome

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

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

Eva

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

ker9

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

Eva

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

ker9

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?

Eva

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

ker9

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

ker9

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)

Paul Newcome

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.

ker9

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

Paul Newcome

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

Paul Newcome

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

ker9

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

Paul Newcome

Multi-select 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).

ker9

@Paul Newcome - BEAUTIFUL formula! Thank you for the lessons!