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

M Wheeler Ithaca NY

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

S. H. Drew

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

GantryG

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
 
Top