Exclude a row from a SUM
 

I would like to SUM a long column of numbers. However, I would like to
insert a subtotal half way down the column. Of course, I do not want that
subtotal to be included in the SUM total. When I insert that subtotal row,
can I tell Excel somehow to ignore/exclude that row from the SUM
calculation.

Thank you

Exclude a row from a SUM
 

You could have

=SUM(a1:A100) - A35

assuming your subtotal row is 35.

Hope this helps.

Pete

Andrew Dyson wrote:

I would like to SUM a long column of numbers. However, I would like to
insert a subtotal half way down the column. Of course, I do not want that
subtotal to be included in the SUM total. When I insert that subtotal row,
can I tell Excel somehow to ignore/exclude that row from the SUM
calculation.

Thank you

Exclude a row from a SUM
 

check Subtotal() in help. You can make fairly complicated structures using
he subtotal function.

"Andrew Dyson" wrote:

I would like to SUM a long column of numbers. However, I would like to
insert a subtotal half way down the column. Of course, I do not want that
subtotal to be included in the SUM total. When I insert that subtotal row,
can I tell Excel somehow to ignore/exclude that row from the SUM
calculation.

Thank you

Exclude a row from a SUM
 

Thank you all.

I think I must have mistakenly initiated a conversation privately outside of
the group by replying instead of replying to the group, which isn't very
helpful for anyone else reading the thread. So I have added Pete's answer
below.

Andrew

----------------------------------------------------------------------------
An alternative approach, which might make it easier to follow, is to give
the cell with the subtotal in it a name, e.g. "Total". Then your formula
would become:
=SUM(A1:A100) - Total
Another approach: If in the first subtotal cell you have used the formula:
=SUBTOTAL(9,A1:A34)
then for the other overall total you could use:
=SUBTOTAL(9,A1:A100)
and this will ignore any intermediate subtotals you might have in your
range.
Hope this helps.
Pete
----------------------------------------------------------------------------