Excel subtotal for blank cells

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.

Assuming: `ColumnA` is ‘spare’; values are in `ColumnB`; `Row1` contains a label, and `C1` has something in it (say ’Sum’).

Requires identification of value ‘blocks’, so filter `A:C`, untick `(Blanks)` in `B`, key say ‘v’ into `A2` and copy down to last blue row number.

Select `A1:C{last blue row number}`, remove filter (if grouping is required), subtotal with At each change in: ‘(Column A)’, Use function: ‘Sum’, Add subtotal to: tick ‘Sum’,`OK`.

Delete blank rows (eg by filtering `ColumnA`), then Delete `ColumnA`.

Select `ColumnB`, Find what: ‘,B’, Replace with: ‘,A’, click `Replace All`.

Grand total is a bonus.

-
Thank you very much. I learned a lot more about Excel through this answer! – picakhu Oct 11 '12 at 18:35
@picakhu My pleasure. I was hoping so! Complex formulae (even sometimes 'monsters') may be the only practicable solution at times but unless I know how to write such a formula I tend to feel it is safer to string together more familiar steps. – pnuts Oct 11 '12 at 19:11

I came up with this monster, but it works fine, you can just copy it down:

``````=IF(ISBLANK(A4),SUBTOTAL(9,OFFSET(B4,IFERROR(MATCH(0,\$B\$1:B3,-1),1)-ROW(),-1,ROW()-IFERROR(MATCH(0,\$B\$1:B3,-1),1),1)),"")
``````

This is my original (german):

``````=WENN(ISTLEER(A4);TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B4;WENNFEHLER(VERGLEICH(0;\$B\$1:B3;-1);1)-ZEILE();-1;ZEILE()-WENNFEHLER(VERGLEICH(0;\$B\$1:B3;-1);1);1));"")
``````

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.

Edit

In pseudo/explained code:

`IF(ISBLANK(A4),SUBTOTAL(),"")`

If the cell of the current row in column A is empty, calculate the subtotal, otherwise show nothing.

`SUBTOTAL(9,RANGE)`

Create the subtotal using the sum function (9) for the given RANGE

`RANGE = OFFSET(B4,ROWS,COLS,HEIGHT,WIDTH)`

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:

`OFFSET(B4,-3,-1,3,1)`

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:

`IFERROR(MATCH(0,\$B\$1:B3,-1),1)-ROW()`

`ROW()-IFERROR(MATCH(0,\$B\$1:B3,-1)`

which are basically the same, except the sign.

`MATCH(0,\$B\$1:B3,-1)` searches for anything above 0 (-1 means bigger) in column b, from the first cell to the previous of the current one (\$B\$1:B3).

Because this will result in an error for the first subtotal, the `IFERROR` provides 1 to correct this.

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.

-
thanks a lot for this, but I can't get it to work. I get the error #NAME? – picakhu Oct 11 '12 at 14:36
In addition, this may be a translation error, but there is no "SUBSUM" in my Excel. – picakhu Oct 11 '12 at 14:38
you might want to check these formulars by parts, there might be a translation errror of some kind and try to paste it into the cell with the `37` in your example. I'll check it again. – Jook Oct 11 '12 at 14:38
yes - and no ISEMPTY -> ISBLANK is what this should be; SUBSUM -> SUBTOTAL; I edited my answer – Jook Oct 11 '12 at 14:40
Thanks that worked. – picakhu Oct 11 '12 at 14:45