# How do I apply formulas to subtotal rows?

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
```

```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?

-

## migrated from stackoverflow.comApr 20 '13 at 15:08

This question came from our site for professional and enthusiast programmers.

Can you post an example of what's in one of your subtotal rows and what you'd like to see? – Melanie Apr 19 '13 at 21:01
please show me an example – user583576 Apr 19 '13 at 21:05
Generally this is done the other way around: you apply subsequent formulas to just the data rows and skip the subtotals. Most of the time that should work out to the same result. – RBarryYoung Apr 19 '13 at 21:08
for some calculations you need the subtotal within the calculation. weighted averages is one of many examples – sdfor Apr 19 '13 at 21:16

The subtotal rows are generally identifiable (eg with `Average` or `Count` - or most often and as in your case with `Total`). Filter on the 'At each change in column` for contains "Tot" to access just the subtotal rows.

Note that while filtered the results may not look correct but should adjust appropriately when the filter is removed.

-

I had to do a similar thing for a weighted decision matrix with Categories that were individually weighted. The spreadsheet looks something like this:

Where the "#NAME?" thing appears are several formulas that I'll describe. The spreadsheet relies on macros--once they are enabled, the "#NAME?" thing goes away and the correct number appears.

Each category has several criteria with weights. Below the criteria, the category totals are calculated, but in the row to the left of the totals is a cell with the word "Category". The category scores use this formula:

``````=ROUND(SUMPRODUCT(range_up(\$C83),range_up(D83))/SUM(range_up(\$C83)),1)
``````

The formula above would be correct if it was entered in cell D84, and the category weight was in \$C84.

The final scores use this formula:

``````=sum_categories(\$B4:\$B98,1,2)
``````

where "\$B4:\$B98" is the range that contains the word "Category", the "1" is how many columns to the right of "Category" column the weights appear, and the "2" is how many columns to the right of the "Category" column the scores appear (i.e., this should be the column the formula is in).

The formulas above use two function, range_up and sum_categories, provided below:

``````Function range_up(r As Range) As Range
Dim t As Range, b As Range

Application.Volatile

Set b = r.Cells(1, 1) 'make sure it's only one cell

If IsEmpty(b.Value) Then 'if cell is empty, start one cell up
Set b = b.offset(-1)
End If

'end(xlup) has strange behaviour if cell above is blank, so fix it manually
If IsEmpty(b.offset(-1)) Then
Set t = b
Else
Set t = b.End(xlUp)
End If
Set range_up = t.Resize(b.Row - t.Row + 1)

End Function

Function sum_categories(r As Range, offset1 As Integer, offset2 As Integer) As Variant
Dim sum As Variant
Dim c As Range

Application.Volatile

sum = 0
For Each c In r.Cells
If c.Value = "Category" Then
sum = sum + c.offset(0, offset1).Value * c.offset(0, offset2).Value
End If
Next c

sum_categories = sum
End Function
``````

Finally, if you want to recalculate manually, include a button on your form that calls this function:

``````Sub force_recalc()
Application.CalculateFullRebuild
End Sub
``````
-

Is the problem just in presentation? If so, then you could use a simple conditional to hide the unwanted output. `=IF(RIGHT(B10,5)="Total",INDEX(Summary,A10),"")` (Substitute the INDEX with whatever formula you're actually using)

-

If you collapse all the rows you do not want to overwrite, you can paste the formula by doing the following:

1. Copy the formula
2. Highlight the area you want to apply the formula to
3. Go to "Go to special" (top-right of home ribbon, or CtrlSG then Alt+S)
4. Select visible cells only, hit ok
5. Paste the formula

Hope this helps

-