| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grand total based on sub-total
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grand total based on sub-total
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 da wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grand total based on sub-total
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) "da" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grand total based on sub-total
My last posting I summed column A instead of column C.
=SUMPRODUCT(--(A2:A88<A3:A89),C2:C88) "da" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grand total based on sub-total
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" wrote: 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 da wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grand total based on sub-total
Did the =sum()/2 work ok, then?
da wrote: 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" wrote: 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 da wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
| Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
| Pivots - Auto calc % Sub total is of grand total | Excel Discussion (Misc queries) | |||
| Grand total based on conditions within an array | Excel Discussion (Misc queries) | |||
| Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
Linear Mode
