# Excel: Is it possible to have rows with sub-totals that doesn't ruin the total-total?

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

``````=subtotal(9,YourRange)
``````

``````=sum(YourRange)
``````

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

-

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

-