| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
Hi,
Hard to tell what's wrong but I would use a Pivottable for the 3 subtotals. Ola |
|
#3
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF function displays wrong amount | Excel Worksheet Functions | |||
Linear Mode
