I was hoping I could get some help with an Excel function. I've searched a lot but can't find an answer. I'm not sure if this is possible, but here goes...

My question is:

**Is it possible to use the Subtotal function to only add the first (or second) number found in cells containing data like the following? Effectively, transforming the cell value before adding?**

This is what the cells will contain:

0.5 (1.6)

I'm trying to get one Subtotal function that will add the first number in these cells, and another Subtotal function that will add the second number in these cells.

__Please note that I am unable to use a helper column to separate out the numbers in different columns.__

The detail is:

I have the following function:

Excel Formula:

`=(SUBTOTAL(9,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!$B$1)))-(SUMPRODUCT((ISNUMBER(SEARCH("Lv",INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),"1","")&Config!$B$1)))*(SUBTOTAL(9,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11"),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!$B$1))-MIN(ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!$B$1))),0))))))+((SUMPRODUCT((OR(ISNUMBER(SEARCH({">*Lv","Lv>"},INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),"1","")&Config!$B$1))))*(SUBTOTAL(9,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11"),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!$B$1))-MIN(ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"11:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!$B$1))),0)))))))`

It is a Subtotal function that:

- adds all values in a column;
- subtracts any values in the same column, if the corresponding value in the
*next column*contains "Lv"; and - adds back any values in the same column, if the corresponding value in the
*next column*contains ">*Lv" or "Lv>".

For Step 3, the cells to add will be formatted like this: "0.5 (1.6)"

I need to have subtotal functionality to only add the first number. I also need a function that will alternatively add only the second number.

Any help with this would be greatly appreciated!

Thanks