Nested Subtotals in Excel 2003

K

KenCarter

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.
 
D

Dave Peterson

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.
 
C

cmcfalls

I am having the same problem. I work for a large company and they hav
released Office 2003 for distribution to everyone and we are all havin
this problem. If your data is sorted correctly and your subtotal
worked before, I'd say its a bug in Excel 2003 that will have to b
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 a
1s
 
G

Guest

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.
 
G

grant

Helen in Tucson said:
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.
Click to expand...
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.

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.
 
G

Guest

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
 
K

Ken Carter

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

His answer was:

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.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nested Subtotals 3
Secondary subtotals 6
Subtotals Nightmare in Excel 2003 4
Subtotals: Nested subtotals below higher subtotal 1
Excel 2003: Conditional Formatting with Subtotals 1
subtotals with custom autofiltering 1
Presenting Subtotals only 2
subtotals showing 0 in summary level// 2 subtotal levels 2

Top