Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

migrated from stackoverflow.com Apr 20 '13 at 15:08

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

3 Answers

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.

SU585375 example

share|improve this answer
add comment

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

excel screenshot

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
share|improve this answer
add comment

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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.