| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Subtotals / Grand Total
This should be so simple but I cant figure out how to get a Grand Total cell
B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
Try something like this:
B16: =SUMIF(A1:A15,"",B1:B15) OR....if you have some control over the intermediate totals... Use the SUBTOTAL function. The SUBTOTAL function ignores other SUBTOTAL functions in it's referenced range. Example: B5: =SUBTOTAL(9,B2:B4) B10: =SUBTOTAL(9,B7:B9) B14: =SUBTOTAL(9,B12:B13) and the grand total would be: B16: =SUBTOTAL(9,B1:B15) That formula will ignore cells B5, B10 and B14. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dp" wrote in message ... This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
Sorry - Excel 2000
"dp" wrote: This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
There's a couple ways you can do this.
First you can, in your "Grand Total" cell type =SUM( then click on each cell you want it to add while holding the control key. Once you've clicked on all your sub-totals, press Enter. Another way is to layout your data differently. Row 1=Company Row 2=January Row 3=February Row 4=March Row 5=Sub-total At the end of your Sub-totals (assuming you'll be in E5, with data in E2 through E4) you can now do the formula =SUM(E2:E4) You can also lay the data out in Column form as well Column A=Company Column B=January Column C=February Column D=March Column E=Sub-total Since all your sub-totals will be in colum E, you can auto-sum on that. "dp" wrote: This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
Eureka! The SUBTOTAL function worked. Once I figured out that the function I
needed was 9 and selected the B column for ref1 it worked. Thank you. "Ron Coderre" wrote: Try something like this: B16: =SUMIF(A1:A15,"",B1:B15) OR....if you have some control over the intermediate totals... Use the SUBTOTAL function. The SUBTOTAL function ignores other SUBTOTAL functions in it's referenced range. Example: B5: =SUBTOTAL(9,B2:B4) B10: =SUBTOTAL(9,B7:B9) B14: =SUBTOTAL(9,B12:B13) and the grand total would be: B16: =SUBTOTAL(9,B1:B15) That formula will ignore cells B5, B10 and B14. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dp" wrote in message ... This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
Hi
In addition to using the Subtotal function you could also use =SUM(B1:B15)/2 -- Regards Roger Govier "dp" wrote in message ... This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
I'm glad I could help......and thanks for the feedback.
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dp" wrote in message ... Eureka! The SUBTOTAL function worked. Once I figured out that the function I needed was 9 and selected the B column for ref1 it worked. Thank you. "Ron Coderre" wrote: Try something like this: B16: =SUMIF(A1:A15,"",B1:B15) OR....if you have some control over the intermediate totals... Use the SUBTOTAL function. The SUBTOTAL function ignores other SUBTOTAL functions in it's referenced range. Example: B5: =SUBTOTAL(9,B2:B4) B10: =SUBTOTAL(9,B7:B9) B14: =SUBTOTAL(9,B12:B13) and the grand total would be: B16: =SUBTOTAL(9,B1:B15) That formula will ignore cells B5, B10 and B14. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dp" wrote in message ... This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Subtotals / Grand Total
Clever - more surprising - I understand!
"Roger Govier" wrote: Hi In addition to using the Subtotal function you could also use =SUM(B1:B15)/2 -- Regards Roger Govier "dp" wrote in message ... This should be so simple but I cant figure out how to get a Grand Total cell B16 without naming each sub total cell. I used 'auto sum' icon to get a subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because I got a sum that included the sub totals as another entry. Obviously I am a very new Excel user. TIA Col A Col B Mr. Smith January 5.00 February 6.00 March 7.00 18.00 ABC Co January 15.00 February 20.00 35.00 DEF Co January 10.00 10.00 Grand Total ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| grand total subtotals without hiding details | Excel Worksheet Functions | |||
| Help, add subtotals included in one column to get a grand total | Excel Discussion (Misc queries) | |||
| Removeing the Grand Total Line when subtotals are removed | Excel Discussion (Misc queries) | |||
| show only the subtotals and grand total in a pivot table | Excel Worksheet Functions | |||
| grand-total of subtotals ? | Excel Worksheet Functions | |||
Linear Mode
