Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ---------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude more than one row from data-Sort | Excel Discussion (Misc queries) | |||
Calc RSQ and exclude zeros | Excel Worksheet Functions | |||
Exclude Header from Range? | Excel Discussion (Misc queries) | |||
how to exclude data outliers from formula or chart without deleti. | Excel Discussion (Misc queries) | |||
Schedule to exclude weekends and holidays | Excel Discussion (Misc queries) |