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




I would like it to return subtotals like


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:


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.

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 .