Multi-Level Subtotals Not Ordering Correctly (Not VBA)

[markkramer]

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

 

[GlennUK]

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.

 

[GlennUK]

Blimey, found it ( fingers crossed ):

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

 

[markkramer]

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]

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.