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

4 Answers 4

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

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

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

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.