Fun with SubTotals

[OldDog]

Hi,

I am trying to run a subTotal function on two columns of data.
Column 2 has our Techs names and Column 3 has status info.

First I subtotal the Techs and then the status. Everything works well
in Excel 2007.
However in Excel 2003, some of the Status subTotals show up above the
Tech count and some of them
are below the tech count. This of course makes my scripts produce
erroronius data.

Is this a problem with Excel 2003 or do I have to do some other magic
to make the results come out in a consistent mannner?

Notice Tech 5 and 6, the Service Restored Count shows up below Tech5
Count and Tech6 Count ;

Problem ID + Assignee Status D/T Reported + Severity
Tech1 None
None Count 0
PRB000007740911 Tech1 Service Restored 4/21/2007 4:45 5
Service Restored Count 1
Tech1 Count 3

Tech2 None
None Count 0
PRB000007747914 Tech2 On Hold 4/24/2007 15:58 4
On Hold Count 1
Tech2 Count 3

Tech3 None
Tech3 Count 1
None Count 0

Tech4 None
Tech4 Count 1
None Count 0

Tech5 None
None Count 0
PRB000007748287 Tech5 Service Restored 4/24/2007 17:43 3
Tech5 Count 3
Service Restored Count 1

Tech6 None
None Count 0
PRB000007751174 Tech6 Service Restored 4/25/2007 15:30 4
Tech6 Count 3
Service Restored Count 1

 

[Bernie Deitrick]

OldDog,

From Microsoft.com: "Calculations are correct, and the correct subtotals and grand totals are
created. Only the location has changed."

So, you really are stuck, but you could modify your code to find the values of interest, or use
SUMPRODUCT formulas to calculate the values that you use. Since you don't say how you are using the
values, it is hard to offer advice.

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Maybe...

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

 

[Jay]

Hi OldDog -

Review the kb article at this location to see if the fix that Microsoft
suggest is right for you (Multilevel subtotals are in the wrong position in
Excel 2002 and in Excel 2003):

http://support.microsoft.com/kb/831824

I've experienced the problem you describe using Excel 2003-SP2 and Steps 1-9
in the article solved the problem properly and permanently. As the artcle
notes, the fix requires a straightforward registry modification, so practice
safe computing and back up your registry before hand in case you have
trouble. The article above contains the following link that describes how to
backup and restore the registry:

http://support.microsoft.com/kb/322756/.

Two final notes to be aware of. First, the registry modification can change
the location of subtotals in existing files (if they were located as desired,
they can be displayed in undesired locations after the fix, especially if
subtotals were previously displayed above the subtotaled group instead of
below it). In other words, all Excel 2003 files opened after the registry
modification will be subjected to the modification; if they contain subtotal
formulas, there is the potential that they may display unexpectedly. The
files themselves are unaffected and restoring the registry restores proper
subtotal display in such files. You'll have to check your files for correct
subtotal operation to verify that the modification works for you, but it
worked flawlessly in my case.

Second, I vaguely recall that the modification above eliminates the option
to display subtotals above the subtotaled data; once the registry is
modified, you can only display subtotals below (again, this is a vague
recollection so verfy this in the articles and links if it applies to you).