I have a spreadsheet with many subtotals and a variable number of rows between them. If I show only the subtotal lines, add a formula and drag it down, Excel adds the formula to all lines not just the subtotal lines:-

before subtotal:

prod qty price 1 3 55 1 4 66 2 5 77 2 6 88 2 7 99 2 8 110 2 9 121 3 10 132 3 11 143 3 12 154

after sub total compressed:

prod qty price 1 Total 7 121 2 Total 35 495 3 Total 33 429 Grand Total 75 1045

added average to subtotals:

prod qty price weighted average 1 Total 7 121 17.28571429 2 Total 35 495 14.14285714 3 Total 33 429 13 Grand Total 75 1045

dragged down formula and uncompressed

prod qty price weighted average 1 3 55 1 4 66 1 Total 7 121 17.28571429 2 5 77 15.4 2 6 88 14.66666667 2 7 99 14.14285714 2 8 110 13.75 2 9 121 13.44444444 2 Total 35 495 14.14285714 3 10 132 13.2 3 11 143 13 3 12 154 12.83333333 3 Total 33 429 13 Grand Total 75 1045

Please don't concentrate on this example and how it can be done differently. The actual sheet is very complex and we need to use the subtotals in formulas.

I need to apply formulas just to the subtotals. How do I do that?