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