Multi-Level Subtotals Not Ordering Correctly (Not VBA)

markkramer

Board Regular
Joined
May 8, 2002
Messages
162
We have a strange subtotaling situation and I was wondering if anyone else has come across it or found a way around it.

We are creating a multi-level subtotal. What I mean like that is that we have a workbook with 7 columns in it. We first subtotal by column A, then subtotal by column B, then by column C, then D, and finally by E. For columns B-E we do not check "replace current subtotals". For all of them we sum on column F.

When we have done this in the past the subotals have aligned correctly from column E into column A so that a group of subtotals in column E add up to a subotal in column D. A group of subtotals in column D add up to a subtotal in column C. And so on. Looking at what is subtotaled, it looks nicely "angled" like this:

................subtotal column E
............subtotal column D
.........subtotal column C
.....subtotal column B
subtotal column A

But now, for some reason, it is a strangely subtotaled. It does column A and B correctly but consistently messes up beginning with column C putting things in backwards order. Like this:

.....subtotal column E
.........subtotal column D
............subtotal column C
................subtotal column B
subtotal column A

I have checked to make sure that the worksheet is sorted and that there are no duplicates.

Does anyone have any other ideas what might be causing this?

Thanks!

Mark
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Yes, I've had this before. There was a fix on the Microsoft site ( I think ). What version of Excel are you using?

I might not have time today or over the weekend to go and look for the Microsoft site article for the fix, but if you still need help next week, post back.
 

markkramer

Board Regular
Joined
May 8, 2002
Messages
162
It just occurred to me that I never responded to the solution posted to let you all know that this worked. Microsoft's solution for nested subtotals or multilevel subtotals not subtotaling in the correct order is to modify regedit to use Excel97 subtotaling rules. Microsoft does not explain why the problem exists or what causes it to exist but, at least, the solution works.

M
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I'm glad that the solution worked! ( well done memory cells )

It's one of those bugs that are really annoying ... Excel not doing what it's supposed to do.
 

Forum statistics

Threads
1,136,878
Messages
5,678,301
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top