Sum function for non-continuous range

[Guest]

Hello,
I have a sheet with data that is organized by program. For each program I
have a subtotal. At the very top I add up all the subtotals to a grand total.
As the subtotals are non-continuous and not at a regular intervall, I have so
far used a plain Sum function. However, I found an alternative, which is as
follows:
SUM(A1:A500)/2
It works just the same but I do not understand how. Can somebody please
explain?

TIA.
Regards,
Antje Crawford

 

[Anne Troy]

Because you have subtotals in the same column.
If I have 3 and 4 in a column, and the subtotal in the same column, which is
7, then 3+4+7=14. If I divide 14 by 2, I get my subtotal anyway, which is 7,
which is correct.
*******************
~Anne Troy

www.OfficeArticles.com

 

[CLR]

Another way to SUM non-contiguous cells is to create a Non-Contiguious
RANGENAME by holding down the Ctrl key while selecting the cells to be
included then doing Insert > Name > Define and giving that range a Name,
then use.........

=SUM(RangeName)

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

One more way:

Use the Subtotal() function throughout for your subtotals, and also for your
grand total. Subtotal ignores other subtotal() formulas within the list.

Subtotal() does different things, i.e., averages, counts, etc. To make it
sum, the syntax is

=SUBTOTAL(9, range)

You can replace the SUM()s in your list by searching for "=SUM(" and
replacing with "=SUBTOTAL(9,"