Subtotal function--transform data before adding

junbuggle

New Member
Joined
Mar 9, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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:

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:
  1. adds all values in a column;
  2. subtracts any values in the same column, if the corresponding value in the next column contains "Lv"; and
  3. adds back any values in the same column, if the corresponding value in the next column contains ">*Lv" or "Lv>".
There's a long story why I am doing things this way, but it may be easiest for this question if I don't go into the detail.

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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
Subtotal will only work with one value in a cell, not two.

Unless you need the option to ignore hidden rows or previous subtotal formula results, you do not need the subtotal function, instead you would need to split the string using left or mid functions.

If you do need the subtotal function then you will need an extra column, there is no way of doing it without.
 

junbuggle

New Member
Joined
Mar 9, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Darn, okay thank you. I was hoping there might be some clever way to get around the need for a helper column - I t’s a huge spreadsheet and adding columns isn’t really possible due to the way it’s been constructed. I’ve just inherited it...

And yes, I do need the option to ignore hidden rows, hence me using Subtotal.

Thanks anyway.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
To help you understand why it will not work.

Subtotal and Aggregate (the only functions that can ignore hidden rows) can only do so with range references, e.g. SUBTOTAL(9,A1 or AGGREGATE(9,5,A1

You can use functions that return a range in place of the range reference, as you have done with INDIRECT in your current formula, but you can not use arrays in place of the range reference. This means that you can not refer to part of the cell as a range reference refers directly to the cell and the entire content.

AGGREGATE can work with arrays (only function numbers that use the optional [k] argument), or hidden rows, but not both at the same time.

The one way that you 'might' be able to work around it would be with something like this, but it is far from efficient. Personally, this is not something that I would attempt, but when you have to work with inherited carnage there are often many necessary evils. The formula will work in basic format, but whether or not it will work when adapted to everything else that you need in terms of criteria is a whole different story.

Note that this is a very simple example to take whatever is before the first space in the range A1:A100 and then subtotal what has been found.
Excel Formula:
=SUMPRODUCT(IFERROR(--LEFT(A1:A100,FIND(" ",A1:A100)-1),0)*SUBTOTAL(103,OFFSET(A1,ROW(A1:A100)-1,0,1,1)))
Also, you will see that I have used 103 instead of 9 as the SUBTOTAL function, this is used to count each visible row as 1 and each hidden row as 0 rather than using it to sum what is in the cell.
 

junbuggle

New Member
Joined
Mar 9, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

but when you have to work with inherited carnage there are often many necessary evils.

This is so true. In a perfect situation, many things are possible. But things can be difficult when you are constrained due to certain factor, and have to be inventive to get things to work.

Your suggestion works! Thank you so much - I would never have thought to try something like this. You can educate yourself about how formulas work, but applying them is something very special. I really appreciate the time you took to reply!

One final question: I updated your formula slightly, and now it works for my purposes:

Excel Formula:
=SUMPRODUCT(IFERROR(--LEFT(AF1:AF200,FIND(" (",AF1:AF200)-1),0)*(ISNUMBER(SEARCH(">*Lv",AG1:AG200))*SUBTOTAL(103,OFFSET(AF1,ROW(AF1:AF200)-1,0,1,1))))

My only remaining problem is when I update the starting row. If I change AF1/AG1 to AF11/AG11, the formula stops working. Could you please help me to understand why?

Does this have something to do with the Offset formula? But I thought that was a relative reference?

I'd be really grateful if you had any insights into this.


Thanks! Seriously... I'm just awed at this 'workaround'. Perhaps 'clunky' in that it requires errors to function... but so elegant in solving a problem with pre-existing limitations!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
Does this have something to do with the Offset formula? But I thought that was a relative reference?
That is correct, as I said, I kept it simple in order to show you the theory (perhaps a little too simple).

In the offset part, the first row has to balance out to zero.

OFFSET(AF1,ROW(AF1:AF200)-1 works fine with row 1, but for row 11 it needs to be OFFSET(AF11,ROW(AF11:AF200)-11 or the more practical way to copy and paste the formula cell,
OFFSET(AF1,ROW(AF1:AF200)-ROW(AF1)

Perhaps 'clunky' in that it requires errors to function
Not strictly true, the part that is used to split the cell, --LEFT(A1:A200,... will result in an error if there are any rows that don't contain " (". The error part is there to set those rows to a zero value to prevent false results in the final formula.

Hopefully you have enough to work with, but I'll keep your thread on my watch list in case you run into trouble.
 
Solution

junbuggle

New Member
Joined
Mar 9, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thanks so much @jasonb75! I tried this with your advice to reference the current row and it's working fine :) Again, I really appreciate you helping me out here. This is exactly what I was after!

Just curious - what is the OFFSET actually doing? And is there a simple explanation as to why the 103 in SUBTOTAL is actually giving a sum figure? Only if you have a minute to spare.


Thanks!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
is there a simple explanation as to why the 103 in SUBTOTAL is actually giving a sum figure?
It's not actually giving a sum figure, this is where the formula trickery comes into play.

Subtotal 103 performs a count based on whether the row is visible or hidden, a visible row will have a count of 1, a hidden row will have a count of 0. (see offset notes at the end).
The LEFT() part of the formula extracts the actual value from each row (visible and hidden), which is then multiplied by the count of 1 or 0 from above. In turn this means multiplying hidden values by 0 which leaves a net value of 0 for that row.
Sumproduct then adds the values of each row to give your sum total.
Just curious - what is the OFFSET actually doing?
If you used subtotal without offset then the count that I mentioned would simply be the total number of visible rows in the range rather than each individual row, the use of offset allows us to manipulate that count into a row by row array that can be used with the rest of the formula.

If you had 20 visible rows then it would multiply all values (visible and hidden by 20) then sum them all, which would give a very wrong result :oops:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,372
Messages
5,635,880
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top