Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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
    
@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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.