| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotal within subtotal
Hi... I have a worksheet of data that I want to sort by Dealer number and
then by quarter (Q1, Q2, Q3, Q4). I am trying to subtotal by dealer number and have the sum of each quarter contained within. I subtotaled by dealer. Then I subtotaled again by quarter ... without replacing previous subtotal. It seemed to work. But in the middle of the data it blew up and started counting Q4 of the one dealer in the subtotal of the next dealer and on and on. So the subtotals by quater were correct, but the subtotals by dealer were inaccurate. EX dealer 1=Q1+Q2+Q3.... dealer 2 =Q4 dlr 1 +Q1+Q2+Q3. Please help!!! I can't figure out where it's bombing out. Thanks, Chris -- Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal within subtotal
actually.. i have a change to my previous post. the dealer subtotals are
correct. however Q4 is appearing under the dealer total instead of above the dealer subtotal with Q1, Q2 and Q3. Again, the dealer subtotal adds up to all four Qs but the fourth quarter is appearing beneath the dlr subtotal in the middle of the worksheet. the beginning of the spreadsheet the data was appearing fine. it looks like it bombed out with my first dealer that only had one line of data in Q1. at that point i got a dealer total, but the q1 total appeared beneath it. I'm perplexed. Advice welcomed. Chris -- Thanks! "learningaccess" wrote: Hi... I have a worksheet of data that I want to sort by Dealer number and then by quarter (Q1, Q2, Q3, Q4). I am trying to subtotal by dealer number and have the sum of each quarter contained within. I subtotaled by dealer. Then I subtotaled again by quarter ... without replacing previous subtotal. It seemed to work. But in the middle of the data it blew up and started counting Q4 of the one dealer in the subtotal of the next dealer and on and on. So the subtotals by quater were correct, but the subtotals by dealer were inaccurate. EX dealer 1=Q1+Q2+Q3.... dealer 2 =Q4 dlr 1 +Q1+Q2+Q3. Please help!!! I can't figure out where it's bombing out. Thanks, Chris -- Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal within subtotal
There was a problem with early versions of xl2002 and xl2003 where multi level
subtotals didn't appear in the correct location. Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003 http://support.microsoft.com/default...b;en-us;831824 But that doesn't sound like your problem. If I were a betting man, I'd bet that your data isn't correct. I'd look for extra characters (spaces???) in those offending keys--or maybe the data isn't sorted correctly???? ===== And for a different approach... You may find that using data|pivottable much easier/quicker to use. You can get some very nice summary reports with pivottables. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx learningaccess wrote: actually.. i have a change to my previous post. the dealer subtotals are correct. however Q4 is appearing under the dealer total instead of above the dealer subtotal with Q1, Q2 and Q3. Again, the dealer subtotal adds up to all four Qs but the fourth quarter is appearing beneath the dlr subtotal in the middle of the worksheet. the beginning of the spreadsheet the data was appearing fine. it looks like it bombed out with my first dealer that only had one line of data in Q1. at that point i got a dealer total, but the q1 total appeared beneath it. I'm perplexed. Advice welcomed. Chris -- Thanks! "learningaccess" wrote: Hi... I have a worksheet of data that I want to sort by Dealer number and then by quarter (Q1, Q2, Q3, Q4). I am trying to subtotal by dealer number and have the sum of each quarter contained within. I subtotaled by dealer. Then I subtotaled again by quarter ... without replacing previous subtotal. It seemed to work. But in the middle of the data it blew up and started counting Q4 of the one dealer in the subtotal of the next dealer and on and on. So the subtotals by quater were correct, but the subtotals by dealer were inaccurate. EX dealer 1=Q1+Q2+Q3.... dealer 2 =Q4 dlr 1 +Q1+Q2+Q3. Please help!!! I can't figure out where it's bombing out. Thanks, Chris -- Thanks! -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal within subtotal
Thank you Dave. I do have V2003. I am familiar with pivot tables but had
apparent dementia this morning. Pivot table looks great! Thanks for the reminder. Chris -- Thanks! "Dave Peterson" wrote: There was a problem with early versions of xl2002 and xl2003 where multi level subtotals didn't appear in the correct location. Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003 http://support.microsoft.com/default...b;en-us;831824 But that doesn't sound like your problem. If I were a betting man, I'd bet that your data isn't correct. I'd look for extra characters (spaces???) in those offending keys--or maybe the data isn't sorted correctly???? ===== And for a different approach... You may find that using data|pivottable much easier/quicker to use. You can get some very nice summary reports with pivottables. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx learningaccess wrote: actually.. i have a change to my previous post. the dealer subtotals are correct. however Q4 is appearing under the dealer total instead of above the dealer subtotal with Q1, Q2 and Q3. Again, the dealer subtotal adds up to all four Qs but the fourth quarter is appearing beneath the dlr subtotal in the middle of the worksheet. the beginning of the spreadsheet the data was appearing fine. it looks like it bombed out with my first dealer that only had one line of data in Q1. at that point i got a dealer total, but the q1 total appeared beneath it. I'm perplexed. Advice welcomed. Chris -- Thanks! "learningaccess" wrote: Hi... I have a worksheet of data that I want to sort by Dealer number and then by quarter (Q1, Q2, Q3, Q4). I am trying to subtotal by dealer number and have the sum of each quarter contained within. I subtotaled by dealer. Then I subtotaled again by quarter ... without replacing previous subtotal. It seemed to work. But in the middle of the data it blew up and started counting Q4 of the one dealer in the subtotal of the next dealer and on and on. So the subtotals by quater were correct, but the subtotals by dealer were inaccurate. EX dealer 1=Q1+Q2+Q3.... dealer 2 =Q4 dlr 1 +Q1+Q2+Q3. Please help!!! I can't figure out where it's bombing out. Thanks, Chris -- Thanks! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal within subtotal
I'm actually having the same problem with the postition of a subtotal. I am
sorting by supervisor and then pay type. On my computers at work, the last item I am doing the second subtotal on, shows up under the supervisor name. At home it works perfectly. "learningaccess" wrote: Thank you Dave. I do have V2003. I am familiar with pivot tables but had apparent dementia this morning. Pivot table looks great! Thanks for the reminder. Chris -- Thanks! "Dave Peterson" wrote: There was a problem with early versions of xl2002 and xl2003 where multi level subtotals didn't appear in the correct location. Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003 http://support.microsoft.com/default...b;en-us;831824 But that doesn't sound like your problem. If I were a betting man, I'd bet that your data isn't correct. I'd look for extra characters (spaces???) in those offending keys--or maybe the data isn't sorted correctly???? ===== And for a different approach... You may find that using data|pivottable much easier/quicker to use. You can get some very nice summary reports with pivottables. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx learningaccess wrote: actually.. i have a change to my previous post. the dealer subtotals are correct. however Q4 is appearing under the dealer total instead of above the dealer subtotal with Q1, Q2 and Q3. Again, the dealer subtotal adds up to all four Qs but the fourth quarter is appearing beneath the dlr subtotal in the middle of the worksheet. the beginning of the spreadsheet the data was appearing fine. it looks like it bombed out with my first dealer that only had one line of data in Q1. at that point i got a dealer total, but the q1 total appeared beneath it. I'm perplexed. Advice welcomed. Chris -- Thanks! "learningaccess" wrote: Hi... I have a worksheet of data that I want to sort by Dealer number and then by quarter (Q1, Q2, Q3, Q4). I am trying to subtotal by dealer number and have the sum of each quarter contained within. I subtotaled by dealer. Then I subtotaled again by quarter ... without replacing previous subtotal. It seemed to work. But in the middle of the data it blew up and started counting Q4 of the one dealer in the subtotal of the next dealer and on and on. So the subtotals by quater were correct, but the subtotals by dealer were inaccurate. EX dealer 1=Q1+Q2+Q3.... dealer 2 =Q4 dlr 1 +Q1+Q2+Q3. Please help!!! I can't figure out where it's bombing out. Thanks, Chris -- Thanks! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal within subtotal
Did you look at the MS knowledge base article?
snmcpa wrote: I'm actually having the same problem with the postition of a subtotal. I am sorting by supervisor and then pay type. On my computers at work, the last item I am doing the second subtotal on, shows up under the supervisor name. At home it works perfectly. "learningaccess" wrote: Thank you Dave. I do have V2003. I am familiar with pivot tables but had apparent dementia this morning. Pivot table looks great! Thanks for the reminder. Chris -- Thanks! "Dave Peterson" wrote: There was a problem with early versions of xl2002 and xl2003 where multi level subtotals didn't appear in the correct location. Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003 http://support.microsoft.com/default...b;en-us;831824 But that doesn't sound like your problem. If I were a betting man, I'd bet that your data isn't correct. I'd look for extra characters (spaces???) in those offending keys--or maybe the data isn't sorted correctly???? ===== And for a different approach... You may find that using data|pivottable much easier/quicker to use. You can get some very nice summary reports with pivottables. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx learningaccess wrote: actually.. i have a change to my previous post. the dealer subtotals are correct. however Q4 is appearing under the dealer total instead of above the dealer subtotal with Q1, Q2 and Q3. Again, the dealer subtotal adds up to all four Qs but the fourth quarter is appearing beneath the dlr subtotal in the middle of the worksheet. the beginning of the spreadsheet the data was appearing fine. it looks like it bombed out with my first dealer that only had one line of data in Q1. at that point i got a dealer total, but the q1 total appeared beneath it. I'm perplexed. Advice welcomed. Chris -- Thanks! "learningaccess" wrote: Hi... I have a worksheet of data that I want to sort by Dealer number and then by quarter (Q1, Q2, Q3, Q4). I am trying to subtotal by dealer number and have the sum of each quarter contained within. I subtotaled by dealer. Then I subtotaled again by quarter ... without replacing previous subtotal. It seemed to work. But in the middle of the data it blew up and started counting Q4 of the one dealer in the subtotal of the next dealer and on and on. So the subtotals by quater were correct, but the subtotals by dealer were inaccurate. EX dealer 1=Q1+Q2+Q3.... dealer 2 =Q4 dlr 1 +Q1+Q2+Q3. Please help!!! I can't figure out where it's bombing out. Thanks, Chris -- Thanks! -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I Subtotal, then Sort by Subtotal Amounts? | Excel Discussion (Misc queries) | |||
| pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
| Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
| copy subtotal value only, subtotal value can be vlookup by others | Excel Discussion (Misc queries) | |||
| Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Linear Mode
