Grand total based on sub-total

[da]

Hello
Is there any way I can get a grand total at the end of spreadsheet based on
all sub-totals without having to use a formula where I have to list each cell
address of the sub-total?
thanks


ID# HOURS AMOUNT
417 17.80 $944.47
417 17.80 $944.47
417 17.80 $944.47
417 17.80 $944.47
417 71.20 $3,777.88
651 5.00 $318.30
651 5.00 $318.30
657 10.00 $436.10
657 10.00 $436.10
657 10.00 $436.10
657 30.00 $1,308.30
673 4.50 $289.13
673 4.00 $171.32
673 10.00 $428.30
673 10.00 $428.30
673 10.00 $428.30
673 5.00 $214.15
673 43.50 $1,959.50
777 10.00 $445.90
777 10.00 $445.90
777 10.00 $445.90
777 4.00 $178.36
777 34.00 $1,516.06
1276 3.00 $203.64
1276 5.50 $373.34
1276 5.50 $373.34
1276 6.00 $407.28
1276 7.00 $475.16
1276 7.00 $475.16
1276 8.00 $543.04
1276 8.00 $543.04
1276 12.00 $814.56
1276 13.00 $882.44
1276 13.00 $882.44
1276 88.00 $5,973.44
1420 4.00 $261.68
1420 14.00 $915.88
1420 10.00 $436.10
1420 10.00 $436.10
1420 10.00 $436.10
1420 48.00 $2,485.86
1429 7.00 $445.62
1429 7.00 $445.62
1429 7.00 $445.62
1429 7.00 $445.62
1429 10.00 $424.40
1429 10.00 $424.40
1429 10.00 $424.40
1429 4.00 $169.76
1429 62.00 $3,225.44
1494 10.00 $436.10
1494 10.00 $436.10
1494 10.00 $436.10
1494 30.00 $1,308.30
1991 5.00 $318.30
1991 5.00 $318.30
1991 7.00 $445.62
1991 7.00 $445.62
1991 10.00 $424.40
1991 10.00 $424.40
1991 10.00 $424.40
1991 3.00 $190.98
1991 4.00 $169.76
1991 61.00 $3,161.78
2011 10.00 $424.40
2011 10.00 $424.40
2105 5.50 $359.81
2105 7.00 $457.94
2105 7.00 $457.94
2105 7.00 $457.94
2105 7.00 $457.94
2105 7.00 $457.94
2105 7.00 $457.94
2105 10.00 $436.10
2105 10.00 $436.10
2105 3.40 $148.27
2105 70.90 $4,127.92
2295 2.00 $130.84
2295 4.00 $261.68
2295 5.00 $327.10
2295 7.00 $457.94
2295 10.00 $436.10
2295 10.00 $436.10
2295 10.00 $436.10
2295 4.00 $174.44
2295 4.00 $174.44
2295 56.00 $2,834.74

 

[Dave Peterson]

How did you create the existing subtotals?

Did you use data|subtotals?

Or did you use/insert the =subtotal() formulas manually?

Or did you use/insert =sum() formulas manually?

If you didn't use data|subtotals, I'd remove those manual formulas/rows and use
that. Life will be much easier. And you'll get the subtotals and grandtotal,
too!

If you used =sum() and don't want to get rid of them (yech!!!), you could just
add up the total and divide by two.

=sum(b2:b999)/2

 

[Joel]

Try this. The last row of of each ID doesn't match the ID of the next row.
Notice the Not condtional is orffset by 1 row.


=SUMPRODUCT(--(A2:A88<>A3:A89),A2:A88)

 

[Joel]

My last posting I summed column A instead of column C.

=SUMPRODUCT(--(A2:A88<>A3:A89),C2:C88)

 

[da]

THANKS
The sub-totals were derieved from a SAP report; I did not calclulate those
numbers. Also, my spreadsheet has over 4,000 records, and these records
contain sub-totals for each employee.

 

[Dave Peterson]

Did the =sum()/2 work ok, then?

THANKS
The sub-totals were derieved from a SAP report; I did not calclulate those
numbers. Also, my spreadsheet has over 4,000 records, and these records
contain sub-totals for each employee.