I have a large data list with subtotals (in fact with nested subtotals). I
have collapsed the list so that only the subtotals are showing. Next I set up
a few columns to the right of the data list with the intent of applying
certain calculations, again ONLY to the subtotals.

Here lieth the problem: when I set up the formulas for the first subtotal
and attempt to simply drag it down, Excel obviously assumes that the copied
formula applies to the next row of the full-blown, uncollapsed data list. Is
there any way to accomplish the task without having to manually create a
separate table that only displays the subtotaled data? That would be a very
big (and boring) job. Incidentally the row interval between subtotals varies
widely.

If the number of subtotals is small, maybe you could just do the formulas for
one row (multiple cells worth of formulas???).

Then copy those cells
select the next visible row (next subtotal line) and ctrl-v

down arrow
ctrl-v

down arrow
ctrl-v

(and so forth)

====
Or modify your formulas....

Look for a key (Like the word Total in the key column)

I'd show all the rows and use a formula like:
=IF(ISERROR(SEARCH("total",A2)),NA(),"yourformulah ere")
and drag down

Then select that column
edit|goto|special
check Formulas
and keep errors checked, but uncheck Numbers, Text, Logicals.
Then hit the delete key to clear contents of those error cells.

========
On the other hand, you may want to play around with pivottables.

After you create the pivottable, maybe using some of the techniques at Debra
Dalgleish's site would come in handy:

http://www.contextures.com/xlPivot10.html



KG wrote:

I have a large data list with subtotals (in fact with nested subtotals). I
have collapsed the list so that only the subtotals are showing. Next I set up
a few columns to the right of the data list with the intent of applying
certain calculations, again ONLY to the subtotals.

Here lieth the problem: when I set up the formulas for the first subtotal
and attempt to simply drag it down, Excel obviously assumes that the copied
formula applies to the next row of the full-blown, uncollapsed data list. Is
there any way to accomplish the task without having to manually create a
separate table that only displays the subtotaled data? That would be a very
big (and boring) job. Incidentally the row interval between subtotals varies
widely.

--

Dave Peterson

the problem I'm having is that:

1) One of the formulas I am using is counting the total number of items that
make up the subtotal and
2) the number of items that go into the subtotal varies widely; for example,
one subtotal may be from a list of 5 items whereas the next may be from a
list of 45.

I don't see how I can copy and paste a formula that computes a range of 5
items to the next subtotal that is composed of a much larger number of items.

"KG" wrote:

I have a large data list with subtotals (in fact with nested subtotals). I
have collapsed the list so that only the subtotals are showing. Next I set up
a few columns to the right of the data list with the intent of applying
certain calculations, again ONLY to the subtotals.

Here lieth the problem: when I set up the formulas for the first subtotal
and attempt to simply drag it down, Excel obviously assumes that the copied
formula applies to the next row of the full-blown, uncollapsed data list. Is
there any way to accomplish the task without having to manually create a
separate table that only displays the subtotaled data? That would be a very
big (and boring) job. Incidentally the row interval between subtotals varies
widely.

Can you post a small sample of your data, your expected results, and
your formula?

In article ,
"KG" wrote:

the problem I'm having is that:

1) One of the formulas I am using is counting the total number of items that
make up the subtotal and
2) the number of items that go into the subtotal varies widely; for example,
one subtotal may be from a list of 5 items whereas the next may be from a
list of 45.

I don't see how I can copy and paste a formula that computes a range of 5
items to the next subtotal that is composed of a much larger number of items.

I have that the f ollowing sample illustrates my problem:


NAME PRODUCT SIZE QUANTITY
Smith ABC Medium 2,500
Smith ABC Large 4,500
Subtotal ABC 7,000
SMITH GRAND TOTAL 7,000

Jones CBC Medium 1,000
Subtotal CBC 1,000
Jones DECK Small 1,000
Subtotal DECK 1,000
Jones XPK Medium 1,000
Jones XPK Large 2,000
Subtotal XPK 3,000
JONES GRAND TOTAL 5,000

Visualize that I hide all rows, except the subtotals and the customer grand
totals and that I want to analyze the customer grand totals.The specific goal
is to count the number of product subtotals adding up to the custoomer grand
total, using a simple COUNTA formula. My problem is that I cannot drag the
formula for SMITH GRAND TOTAL to the JONES GRAND TOTAL, because one has a
range of one subtotal whereas the other has a range of three subtotals.

I hope that explains the issue.

"KG" wrote:

I have a large data list with subtotals (in fact with nested subtotals). I
have collapsed the list so that only the subtotals are showing. Next I set up
a few columns to the right of the data list with the intent of applying
certain calculations, again ONLY to the subtotals.

Here lieth the problem: when I set up the formulas for the first subtotal
and attempt to simply drag it down, Excel obviously assumes that the copied
formula applies to the next row of the full-blown, uncollapsed data list. Is
there any way to accomplish the task without having to manually create a
separate table that only displays the subtotaled data? That would be a very
big (and boring) job. Incidentally the row interval between subtotals varies
widely.

Why not add a subtotal using countA for the Product column?

When you apply data|subtotals, you can still use Sum as your function, but
include the product column.

Since you're summing, all your:
=subtotal(9,Bx:By)
will be 0's (assuming that all your product codes are text).

So select column B and then
edit|replace
what: (9,
with: (3,
replace all

Then you can use that subtotal in your formula.

I don't know if this will work, since you haven't shared the formula.

KG wrote:

the problem I'm having is that:

1) One of the formulas I am using is counting the total number of items that
make up the subtotal and
2) the number of items that go into the subtotal varies widely; for example,
one subtotal may be from a list of 5 items whereas the next may be from a
list of 45.

I don't see how I can copy and paste a formula that computes a range of 5
items to the next subtotal that is composed of a much larger number of items.

"KG" wrote:

I have a large data list with subtotals (in fact with nested subtotals). I
have collapsed the list so that only the subtotals are showing. Next I set up
a few columns to the right of the data list with the intent of applying
certain calculations, again ONLY to the subtotals.

Here lieth the problem: when I set up the formulas for the first subtotal
and attempt to simply drag it down, Excel obviously assumes that the copied
formula applies to the next row of the full-blown, uncollapsed data list. Is
there any way to accomplish the task without having to manually create a
separate table that only displays the subtotaled data? That would be a very
big (and boring) job. Incidentally the row interval between subtotals varies
widely.

--

Dave Peterson