| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
grand total subtotals without hiding details
Hello,
I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
There is a subtotal formula function as well
=SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
Hi Peo,
What goes in the brackets? "Peo Sjoblom" wrote: There is a subtotal formula function as well =SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
For example
D2:D200 if that is the range you want to sum -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hi Peo, What goes in the brackets? "Peo Sjoblom" wrote: There is a subtotal formula function as well =SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
The Range should be obvious, so I assume your question deals with the "9".
That first argument is a function number. There are 11 different function numbers that can be specified there (actually, 22, 11 that include hidden values and 11 that exclude them). The function number "9" makes the SUBTOTAL the cells in the Range whereas the other function numbers do other things. You should check out the Help files for SUBTOTAL to see everything it can do. Rick "Sherry" wrote in message ... Hi Peo, What goes in the brackets? "Peo Sjoblom" wrote: There is a subtotal formula function as well =SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
Hi Peo,
I tried the following formula =subtotal(9,h2:h690) and I still had to divid by 2. If you can see the error I'm making, would you please point it out. Thanks again! Sherry (all values are in column H; column H also contains the subtotals by department) "Peo Sjoblom" wrote: For example D2:D200 if that is the range you want to sum -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hi Peo, What goes in the brackets? "Peo Sjoblom" wrote: There is a subtotal formula function as well =SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
Did you insert rows that contained subtotals in the middle of your data?
If you did, then those formulas could have been =subtotal(9,h2:h5) =subtotal(9,h7:h19) .... instead of: =sum(h2:h5) =sum(h7:h19) =subtotal() will ignore other =subtotal() formulas. Sherry wrote: Hi Peo, I tried the following formula =subtotal(9,h2:h690) and I still had to divid by 2. If you can see the error I'm making, would you please point it out. Thanks again! Sherry (all values are in column H; column H also contains the subtotals by department) "Peo Sjoblom" wrote: For example D2:D200 if that is the range you want to sum -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hi Peo, What goes in the brackets? "Peo Sjoblom" wrote: There is a subtotal formula function as well =SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
grand total subtotals without hiding details
I think I'm getting it now
I've entered =sum(range), so when I use =subtotal(9,range) it's not ignoring the 'sum' formulas but it does ignore the 'subtotal' formulas and give me the right answer when I use =subtotal(9,range) for all of the departments. Thanks for all your help guys! "Dave Peterson" wrote: Did you insert rows that contained subtotals in the middle of your data? If you did, then those formulas could have been =subtotal(9,h2:h5) =subtotal(9,h7:h19) .... instead of: =sum(h2:h5) =sum(h7:h19) =subtotal() will ignore other =subtotal() formulas. Sherry wrote: Hi Peo, I tried the following formula =subtotal(9,h2:h690) and I still had to divid by 2. If you can see the error I'm making, would you please point it out. Thanks again! Sherry (all values are in column H; column H also contains the subtotals by department) "Peo Sjoblom" wrote: For example D2:D200 if that is the range you want to sum -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hi Peo, What goes in the brackets? "Peo Sjoblom" wrote: There is a subtotal formula function as well =SUBTOTAL(9,Range) -- Regards, Peo Sjoblom "Sherry" wrote in message ... Hello, I have a worksheet with just under 700 lines. This worksheet consists of employees, their department and their payroll life insurance deductions for a specific pay date. I have grouped them by department and subtotaled the deductions. Is there a formula that tells excel I want a grand total that only adds the subtotals (because all values are in one column...I suppose I could sum and divide by 2?) Actually that works! Well, I'll leave the post here in case anyone else is trying to get a solution! Thanks for giving me a forum to talk out my problem! Sherry -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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 | |||
| Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
| grand-total of subtotals ? | Excel Worksheet Functions | |||
Linear Mode
