hello all,
I have been given a spreadsheet that contains a column which irritatingly has both absolute values and ranges. I want to sum the column twice, once to give the lower bound and once to give the upper bound. to make things more complicated, I want to make it conditional on the corresponding items in another column, in fact regions. a toy version of this spreadsheet would look like this:
| Region | Number |
|---|---|
| Atlantic | 10 |
| Med | 20-30 |
| Atlantic | 40-50 |
| Atlantic | 90 |
I started on the lower bound and I tried:
SUMIFS(Number, Region, “Atlantic”, Number, “[0-9]{1,3}”),
But it returned 100, rather than 160.
I tested the regex expression on the cells and it will replace the text string with the right number for each of the relevant cells. it appears that it is not turning the text into a number to then be added. is there a way of doing it or am I going to have to make some extra columns?
I am sorry of this has already been solved. I found some topics that were similar but none gave me the answer.
