I have data in excel which looks like
10 15 12 3 2 5 3
I would like it to return subtotals like
10 15 12 37 3 2 5 5 3 8
Is there a way to achieve this in Excel?
An alternative that might be of some use and is possibly more versatile (also avoids the ‘monster’ in case that is not to hand/easily recreatable) would be to string more familiar operations together.
Requires identification of value ‘blocks’, so filter
Delete blank rows (eg by filtering
Grand total is a bonus.
I came up with this monster, but it works fine, you can just copy it down:
This is my original (german):
So, when the cell in Col A is empty, do a subsum, summing up the obove values, up to the last subsum or 1.
If the forumlar is not clear, feel free to ask about specifics.
In pseudo/explained code:
If the cell of the current row in column A is empty, calculate the subtotal, otherwise show nothing.
Create the subtotal using the sum function (9) for the given RANGE
The range for subtotal is created by offsetting / spanning a range, based on the cell B4.
On this concrete example of B4 this would be like writing:
So, create a range, which is 3 rows above B4 (-3), and 1 col to the left (-1), and is 3 row down/high and 1 col wide.
To calculate -3 and 3, which will differ based on the rows between two empty cells in column A, we use these:
which are basically the same, except the sign.
Because this will result in an error for the first subtotal, the
So, when you're in B4, you search if there is any value above B4, assuming this is a subtotal, and if you can't find any, then you use 1 as your row. 1-4 = -3; 4-1=3 there you go.
|show 2 more comments|