Subtotal of Subtotal displays Grand Total in wrong row

[Thomas Born]

Hi;
I have encountered a strange problem with the subtotal function. I am trying
to create 3 levels of subtotals, which works fine except that it messes up
the first groupings.

I have following source data and would like to have subtotals by Customer
Group, Projecttype and Project Group.

Customer Group Projecttype Project Group Project Revenue
C Group A Projecttype A Group A Project 1 500
C Group A Projecttype A Group A Project 2 600
C Group A Projecttype A Group A Project 3 700
C Group A Projecttype A Group B Project 4 800
C Group A Projecttype A Group B Project 5 900
C Group B Projecttype A Group A Project 1 500
C Group B Projecttype A Group A Project 2 600
C Group B Projecttype A Group A Project 3 700
C Group B Projecttype A Group B Project 4 800
C Group B Projecttype A Group B Project 5 900
C Group C Projecttype A Group A Project 1 500
C Group C Projecttype A Group A Project 2 600
C Group C Projecttype A Group A Project 3 700


I select the source data and choose the subtotal function in Excel with the
options: At each change in Customer Group, Use function sum, Add subtotal to
Revenue, none of the checkboxes are clicked and click Ok. Everything works
as expected at this point.
Then I choose subtotal function a second time, this time I choose the
options: At each change in Projecttype, Use function sum, Add subtotal to
Revenue, none of the checkboxes are clicked and click Ok.

When I now collapse the data and display the first three levels I see
following:

Customer Group Projecttype Project Group Project Revenue
Projecttype A Total 3500
Grand Total 8800
C Group A Total 3500
C Group B Total 3500
Projecttype A Total 3500
C Group C Total 1800
Projecttype A Total 1800


You can see the Grand Total appears after the Projecttype A group. I would
have expected the Projecttype A Total to appear after C Group A Total.

Do I do something wrong, or is this a bug in EXCEL?

Any help is greatly appreciated

Thomas Born
([email protected])

 

[Guest]

Hi,

Hard to tell what's wrong but I would use a Pivottable for the 3 subtotals.

Ola

 

[Debra Dalgleish]

The problem is supposed to be corrected by installing this hotfix:

http://support.microsoft.com/default.aspx?id=833855

You can visit this page on the Microsoft site, to check what Office
updates should be installed:

http://office.microsoft.com/en-ca/officeupdate/default.aspx

 

[Thomas Born]

Thanks for this, I have emails MS support for the hotfix as I couldn't find
it anywhere. You would not know where I can download it?

Thomas

 

[Thomas Born]

Thanks Ola;
However for our requirements subtotals seem to work better. It appears to be
a bug in EXCEL and I have contacted MS support about this.

 

[Debra Dalgleish]

As far as I know, you have to contact Microsoft to obtain the hotfix.
It's not available to download.