Nested Subtotals in Excel 2003

KenCarter

unread,

Apr 5, 2004, 2:47:28 PM4/5/04

to

I have 3 levels of data that I am subtotaling.

An example would be population records such that each
record has a state, county, and city identifier.

Each of the identifiers is numeric, i.e. state = 1 for
Alabama. County = 1 for Abraham, city = 1 for Altoona and
so on.

I have sorted the data on on column a, b, c for state,
county, city.

I first subtotal for changes in column 1 which works
correctly and gives me subtotals by state.

I next subtotal with the break on column 2 without
replacing subtotals and this works correctly giving me
subtotals by county within state.

I finally subtotal with the break on column 3 without
replacing subtotals. The results are now totally
inconsistent.

Some Column 3 cities now fall outside their counties and
break at a different level.

I do not understand why this is happening.

Any help would be appreciated.

Dave Peterson

unread,

Apr 5, 2004, 8:30:45 PM4/5/04

to

My first guess is that your data isn't exactly sorted the way you want.

If that's incorrect, then I don't have a second guess.

But I do have a suggestion. You may want to look at Data|pivottable.

You won't have to sort and you'll end up with just the summary.

If you don't like that, you might be able to find the problem by using a helper
column:

In new column A (A2 and drag down)

=B2&"-"&c2&"-"&d2

Then do one subtotal against that concatenated field.

--

Dave Peterson
ec3...@msn.com

Wheeler Ithaca NY@discussions.microsoft.com M Wheeler Ithaca NY

unread,

Jun 18, 2004, 3:46:02 PM6/18/04

to

We are having the same problem. Unfortunately, our excel 2000 was also replaced over night by excel 2003, and we can't get the nested sub-totals to work either. Any tips, clues, or a patch, will be appreciated, since our year-end is in two weeks.

"cmcfalls >" wrote:

> I am having the same problem. I work for a large company and they have
> released Office 2003 for distribution to everyone and we are all having
> this problem. If your data is sorted correctly and your subtotals
> worked before, I'd say its a bug in Excel 2003 that will have to be
> corrected with a service pack. Our IT people can not fix it either.
>
> At least you can get your level 2s to subtotal correctly, mine stops at
> 1s.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
>

in Tucson@discussions.microsoft.com Helen in Tucson

unread,

Jul 22, 2004, 1:35:06 PM7/22/04

to

I too am having troubles with nested levels. I have tried it again and again. It used to work so easily(!). I did the same process, without really having to think about it, last quarter and it worked fine and easily. I just did an update of Windows XP, and that may be the problem. I am sorting on 4 columns and sub-totalling those columns. It appears the first 3 or 4 names sort one way and the rest sorts another way -- but even that way is not consistent. Very frustrating, but glad to hear it may not just be me! Using Excel 2003 with Office Professional for Windows XP.

Helen in Tucson

unread,

Jul 23, 2004, 3:49:02 PM7/23/04

to

As an update, I went to a co-workers machine (which did not have the latest updates) and the sub-total function worked like a breeze (all sorted and sub-totalled just as expected). It appears to be a bug with the latest update.

grant

unread,

Aug 6, 2004, 3:56:37 PM8/6/04

to

Helen in Tucson <Heleni...@discussions.microsoft.com> wrote in message news:<EEFA88CD-D8D7-47B7...@microsoft.com>...

> As an update, I went to a co-workers machine (which did not have the latest updates) and the sub-total function worked like a breeze (all sorted and sub-totalled just as expected). It appears to be a bug with the latest update.
>
> "Helen in Tucson" wrote:
>
> > I too am having troubles with nested levels. I have tried it
again and again. It used to work so easily(!). I did the same
process, without really having to think about it, last quarter and it
worked fine and easily. I just did an update of Windows XP, and that
may be the problem. I am sorting on 4 columns and sub-totalling those
columns. It appears the first 3 or 4 names sort one way and the rest
sorts another way -- but even that way is not consistent. Very
frustrating, but glad to hear it may not just be me! Using Excel 2003
with Office Professional for Windows XP.
> >
> >
> > "M Wheeler Ithaca NY" wrote:
> >

I got in contact with Microsoft and there is a fix for this that
requires an update to the registry to tell Excel 2003 to use the Excel
97 subtotal method since the new code subtotals incorrectly. I updated
my registry and the subtotaling now works. The registry entry that
needs to be added is:

Add the following registry key to -
HKEY_CURRENT_USER\software\microsoft\office\11\excel\options

Dword = Excel97Subtotals

Value = 1

Be sure to backup the registry before updating.

S. H. Drew

unread,

Nov 1, 2004, 12:15:06 PM11/1/04

to

Yep, I'm having the same problem. Mine is with only 2 levels of data. It
puts the subtotal for the last line in the second set under the subtotal for
the first set. The weird thing is the first subtotal is still correct. For
example,

Dogs 2
Cats 3
Birds 4
Animals 10
Frogs 1

Dave Peterson

unread,

Nov 1, 2004, 7:06:19 PM11/1/04

to

I don't use xl2003, but Debra Dalgleish has posted this:

=======

The problem may 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 which Office
updates should be installed:

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

--

Dave Peterson
ec3...@msn.com

Ken Carter

unread,

Nov 2, 2004, 4:06:15 PM11/2/04

to

Thanks to Grant above for the solution that solved my original problem.

His answer was:

Dave Peterson

unread,

Mar 22, 2005, 8:04:26 PM3/22/05

to

It's pretty rare that an actual MS employee will respond to questions here.

It's mostly just people helping people (not saying that MS employees aren't
people <bg>).

But I'd read the KB article and double check that you did it correctly.

Multilevel subtotals are in the wrong position in Excel 2002 and in Excel 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;831824

GantryG wrote:
>
> Hmmm, we have this problem with a fully-patched Excel 2003 unit, and the
> registry fix below did not seem to help. Any one have any other suggestions
> (MS?)
>
> Thanks,
> -Gantry

--

Dave Peterson

GantryG

unread,

Mar 22, 2005, 5:53:05 PM3/22/05

to

Hmmm, we have this problem with a fully-patched Excel 2003 unit, and the
registry fix below did not seem to help. Any one have any other suggestions
(MS?)

Thanks,
-Gantry