subtotal within subtotal

[learningaccess]

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

 

[learningaccess]

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

 

[Dave Peterson]

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.aspx?scid=kb;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/assistance/2002/articles/xlconPT101.aspx

 

[learningaccess]

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

 

[snmcpa]

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.

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

 

[Dave Peterson]

Did you look at the MS knowledge base article?

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.