SUM not excluding SUBTOTAL lines.

[Tom_R._in_OK]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

For some reason, when I use the sum and subtotal functions, the sum includes the subtotal lines. Once in a while, it seems to work, but it isn't right now.

I had created a table, including subtotals and sums, that did work. Then I went in and added a few lines to be included in the subtotal. Now the Sum function isn't excluding the subtotals. I deleted the Sum formula from the cells, and then went in and typed it in again, hoping it would recalculate, but I still get the same problem.

what I'm doing is ....

A1:A30 is my range of numbers
A10 is =subtotal(9,A6:A9)
A31 is =sum(a1:a30)

A31 should equal exactly 1.00000, but it is including the subtotal line, so I'm getting a result that is > 1.00000.

Thanks for any help or advice.

Tom R.

 

[CyberTaz]

Based on what you describe it *shouldn't* work - ever unless all the
values happen to be ZERO or you have offsetting positive & negative figures.

You're summing a range (A1:A30) which includes the cell (A10) which is
subtotaling cells A6:A9. As a result the content of cells A6:A9 effectively
are being included twice in the Total (A31). It's always more complicated to
sum a column or row when it contains summary calculations.

One option is to create your subtotal in a different location rather than in
the range you're trying to total. However, depending on the nature of your
data, the content in other columns & various other undisclosed variables
it's hard to offer any meaningful suggestions.

Perhaps if you paint a more vivid picture of what kind of data you're
working with and what your actual objective is there are some alternatives
to consider. Which ones work "best" depends on that information.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Tom_R._in_OK]

Hmm. I guess it's another case of my assumptions based on my use of Quattro. In Quattro, any subtotal in a range of summed numbers is excluded (IIRC).

Thanks,

Tom

 

[JE McGimpsey]

Hmm. I guess it's another case of my assumptions based on my use of Quattro.
In Quattro, any subtotal in a range of summed numbers is excluded (IIRC).

In XL, a subtotal within a range that is included in *another* subtotal
is excluded:

A
1 1
2 4
3 =SUBTOTAL(9,A1:A2) ==> 5
4 2
5 8
6 =SUBTOTAL(9,A1:A5) ==> 15, not 20.

 

[Tom_R._in_OK]

Aha! That's just what I was trying to do. Thank you for the help, and apologies for the neophyte question.

Tom