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.