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 an excel sheet which looks something like this:

date        text        value        other-value
date        text        value        other-value
date        text        value        other-value
date        text        value        other-value
totals                    sum          other-sum

The sum is of course a sum of all the values above it. What I am wondering is if there is a way I can add rows with a sub-total at random places through out that list. For example if I would like to have a sub-total for each new month. My problem is of course that the total sum will become way to large because it will also sum the rows with the sub-totals. Does Excel have a feature that can help me so I can have the sub-totals and a correct total-total?

I'm using Excel 2010.

share|improve this question
up vote 8 down vote accepted

If you use

=subtotal(9,YourRange)

instead of

=sum(YourRange)

in all instances, then it won't double count your subtotals. In other words, the subtotal function ignores other subtotal functions.

share|improve this answer
    
So I would use subtotal for the total sum as well? or? – Svish May 16 '10 at 11:15
    
Yup, that seems to work actually! Thanks! – Svish May 16 '10 at 11:17
    
pretty nifty solution ! – Gaby Oct 9 '10 at 13:09

that sounds pretty much like a rollup operation. i don't know if there is a native way in excel, but you could use SUMIF instead of SUM and only add to the sum if the date column is set.

if there is a native way in excel (using pivot tables maybe), feel free to ignore this answer

share|improve this answer

.. you could use another column for this sum ...
(sum does not have to take the above rows.. you can specify which rows to add)

share|improve this answer
    
That is true, but becomes a bit of an issue when there are several columns I need to sum :) – Svish May 16 '10 at 11:16

If you have headings above each column, then have a look at the "Subtotal" jobby in "Data".

You can set it to add in subtotals when another column changes.

share|improve this answer

You must log in to answer this question.

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