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.


If you use


instead of


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

  • So I would use subtotal for the total sum as well? or? – Svish May 16 '10 at 11:15
  • pretty nifty solution ! – Gabriele Petrioli 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


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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