Need to total a large spreadsheet using sub-totals in one co

DORISA · May 16th, 2002, 08:40 AM

How can I get a grand total of sub-totals in a spreadsheet without inserting each individual sub-total cell in the formula? Column A has text labeling departments with the word "Total" in a cell aligning with a cell in Column B which contains the sub-total of each group. Each group has 10 lines. Spreadsheet has 839 lines.

Asala42 · May 16th, 2002, 08:50 AM

Hey,

If your range B2:B839 contains the rawdata detail as well as their individual subtotals, maybe you can you get away with:

=SUM(B2:B839)/2

Adam

yosi · May 16th, 2002, 08:57 AM

use sumif formula
=sumif(sheet1!A:A,b2,sheet1!C:C)
the criteria in cell b2 is *Total*, sumif look for the text between the star sign

[ This Message was edited by: yosi on 2002-05-16 07:58 ]

Scott · May 16th, 2002, 09:06 AM

If you use the formula Subtotal(9,Range) for each group, then you can use the same formula using the entire range (including subtotals) for the grand total.

For example: Your Grand total line could be Subtotal(9,A1:A839). Even if this range included other subtotals, it will not include these in the sum. If you use SUM() however, it will include this, so you have to use Subtotal for each set, and the grand total.

You can also use the Subtotal function under the Data menu.

[ This Message was edited by: Scott on 2002-05-16 08:09 ]

DORISA · May 16th, 2002, 09:39 AM

[quote]
On 2002-05-16 07:50, Asala42 wrote:
Hey,

If your range B2:B839 contains the rawdata detail as well as their individual subtotals, maybe you can you get away with:

=SUM(B2:B839)/2

Adam

This seems to work ok except that my grand total is off very slightly. I added the subtotals manually and double-checked, and I also checked the formulas in each sub-total to make sure those were correct. Any ideas?

Asala42 · May 16th, 2002, 09:51 AM

Hey again,

You stated that you added these numbers manually - Is the number too high or too low? Either way I can see 2 possible problems.

1. Rounding error. Did you punch the numbers in manually or did you actually double check by typing the formula =B4+B14+B22..etc. Calculated totals that show a face value of $400.00 might in reality have a number value of 399.996 for example.

2. It is possible you missed a subtotal? Or similarly, is there any data in B that does not have a subtotal listed.

Hope that helps,
Adam

DORISA · May 16th, 2002, 12:07 PM

[quote]
On 2002-05-16 08:06, Scott wrote:
If you use the formula Subtotal(9,Range) for each group, then you can use the same formula using the entire range (including subtotals) for the grand total.

For example: Your Grand total line could be Subtotal(9,A1:A839). Even if this range included other subtotals, it will not include these in the sum. If you use SUM() however, it will include this, so you have to use Subtotal for each set, and the grand total.

You can also use the Subtotal function under the Data menu.

[ This Message was edited by: Scott on 2002-05-16 08:09 ]

I tried this and it seems that the formula is still picking up other data in the column.
There are two consecutive lines of data under the "Total" line data with a blank line after the "total" line. Each group has this data.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 08:40 AM	#1
DORISA New Member Join Date: May 2002 Posts: 6	How can I get a grand total of sub-totals in a spreadsheet without inserting each individual sub-total cell in the formula? Column A has text labeling departments with the word "Total" in a cell aligning with a cell in Column B which contains the sub-total of each group. Each group has 10 lines. Spreadsheet has 839 lines.

May 16th, 2002, 08:50 AM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hey, If your range B2:B839 contains the rawdata detail as well as their individual subtotals, maybe you can you get away with: =SUM(B2:B839)/2 Adam

May 16th, 2002, 08:57 AM	#3
yosi Board Regular Join Date: May 2002 Posts: 67	use sumif formula =sumif(sheet1!A:A,b2,sheet1!C:C) the criteria in cell b2 is Total, sumif look for the text between the star sign [ This Message was edited by: yosi on 2002-05-16 07:58 ]

May 16th, 2002, 09:06 AM	#4
Scott New Member Join Date: Feb 2002 Location: McKinney Texas Posts: 6	If you use the formula Subtotal(9,Range) for each group, then you can use the same formula using the entire range (including subtotals) for the grand total. For example: Your Grand total line could be Subtotal(9,A1:A839). Even if this range included other subtotals, it will not include these in the sum. If you use SUM() however, it will include this, so you have to use Subtotal for each set, and the grand total. You can also use the Subtotal function under the Data menu. [ This Message was edited by: Scott on 2002-05-16 08:09 ]

May 16th, 2002, 09:39 AM	#5
DORISA New Member Join Date: May 2002 Posts: 6	[quote] On 2002-05-16 07:50, Asala42 wrote: Hey, If your range B2:B839 contains the rawdata detail as well as their individual subtotals, maybe you can you get away with: =SUM(B2:B839)/2 Adam This seems to work ok except that my grand total is off very slightly. I added the subtotals manually and double-checked, and I also checked the formulas in each sub-total to make sure those were correct. Any ideas?

May 16th, 2002, 09:51 AM	#6
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hey again, You stated that you added these numbers manually - Is the number too high or too low? Either way I can see 2 possible problems. 1. Rounding error. Did you punch the numbers in manually or did you actually double check by typing the formula =B4+B14+B22..etc. Calculated totals that show a face value of $400.00 might in reality have a number value of 399.996 for example. 2. It is possible you missed a subtotal? Or similarly, is there any data in B that does not have a subtotal listed. Hope that helps, Adam

May 16th, 2002, 12:07 PM	#7
DORISA New Member Join Date: May 2002 Posts: 6	[quote] On 2002-05-16 08:06, Scott wrote: If you use the formula Subtotal(9,Range) for each group, then you can use the same formula using the entire range (including subtotals) for the grand total. For example: Your Grand total line could be Subtotal(9,A1:A839). Even if this range included other subtotals, it will not include these in the sum. If you use SUM() however, it will include this, so you have to use Subtotal for each set, and the grand total. You can also use the Subtotal function under the Data menu. [ This Message was edited by: Scott on 2002-05-16 08:09 ] I tried this and it seems that the formula is still picking up other data in the column. There are two consecutive lines of data under the "Total" line data with a blank line after the "total" line. Each group has this data.