Hi all,
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:
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:
It is a Subtotal function that:
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
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