Excluding cells

[LoriD]

I have the following:

1/1/88 10 9
2/5/88 9 8
TOTAL 19 17

2/1/89 20 18
4/4/89 15 15
TOTAL 35 33

5/5/90 10 8

GRAND TOTAL 64 58

How do I write a formula so that the Grand Total would add all the numbers
EXCEPT the numbers on the lines of the TOTALS?

THANKS

[ufo_pilot]

in the cell you need the results
=SUM > then hold down the Ctrl and select the cells that you need to sum.

"LoriD" wrote:

> I have the following:
>
> 1/1/88 10 9
> 2/5/88 9 8
> TOTAL 19 17
>
> 2/1/89 20 18
> 4/4/89 15 15
> TOTAL 35 33
>
> 5/5/90 10 8
>
> GRAND TOTAL 64 58
>
> How do I write a formula so that the Grand Total would add all the numbers
> EXCEPT the numbers on the lines of the TOTALS?
>
> THANKS

[Ron Coderre]

Try one of these:

Use the SUBTOTAL function instead of the SUM function for both subtotals and
for the grand total. The SUBTOTAL function ignores other SUBTOTALS.
Example:=SUBTOTAL(9,B1:B10)

The 9 argument tells the subtotal function to ADD. Other arguments indicate
usage of MAX, MIN, AVG, etc)

OR
Use SUMIF.
Example:
=SUMIF(A1:A10,"<>TOTAL",B1:B10)

Does that help?

Regards,
Ron


"LoriD" wrote:

> I have the following:
>
> 1/1/88 10 9
> 2/5/88 9 8
> TOTAL 19 17
>
> 2/1/89 20 18
> 4/4/89 15 15
> TOTAL 35 33
>
> 5/5/90 10 8
>
> GRAND TOTAL 64 58
>
> How do I write a formula so that the Grand Total would add all the numbers
> EXCEPT the numbers on the lines of the TOTALS?
>
> THANKS

[LoriD]

Ron - worked perfectly!!!
THANKS

"Ron Coderre" wrote:

> Try one of these:
>
> Use the SUBTOTAL function instead of the SUM function for both subtotals and
> for the grand total. The SUBTOTAL function ignores other SUBTOTALS.
> Example:=SUBTOTAL(9,B1:B10)
>
> The 9 argument tells the subtotal function to ADD. Other arguments indicate
> usage of MAX, MIN, AVG, etc)
>
> OR
> Use SUMIF.
> Example:
> =SUMIF(A1:A10,"<>TOTAL",B1:B10)
>
> Does that help?
>
> Regards,
> Ron
>
>
> "LoriD" wrote:
>
> > I have the following:
> >
> > 1/1/88 10 9
> > 2/5/88 9 8
> > TOTAL 19 17
> >
> > 2/1/89 20 18
> > 4/4/89 15 15
> > TOTAL 35 33
> >
> > 5/5/90 10 8
> >
> > GRAND TOTAL 64 58
> >
> > How do I write a formula so that the Grand Total would add all the numbers
> > EXCEPT the numbers on the lines of the TOTALS?
> >
> > THANKS