Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
    
Thank you very much. I learned a lot more about Excel through this answer! – picakhu Oct 11 '12 at 18:35

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.

share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .