Sum function for non-continuous range
 

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

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



"Antje" wrote in message
...
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

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



"Antje" wrote in message
...
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

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,"



"Antje" wrote:

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