Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Thomas Born
 
Posts: n/a
Default Subtotal of Subtotal displays Grand Total in wrong row

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
)


  #2   Report Post  
Ola
 
Posts: n/a
Default

Hi,

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

Ola

  #3   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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/of...e/default.aspx



Thomas Born wrote:
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
)




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #4   Report Post  
Thomas Born
 
Posts: n/a
Default

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
"Debra Dalgleish" wrote in message
...
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/of...e/default.aspx



Thomas Born wrote:
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
)




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



  #5   Report Post  
Thomas Born
 
Posts: n/a
Default

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.
"Ola" wrote in message
...
Hi,

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

subtotals.

Ola





  #6   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

Thomas Born wrote:
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
"Debra Dalgleish" wrote in message
...

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/of...e/default.aspx



Thomas Born wrote:

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
)




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF function displays wrong amount JAnderson Excel Worksheet Functions 4 November 6th 04 07:41 PM


All times are GMT +1. The time now is 09:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"