The Daily Dose

Suppose you have a table with a number of columns.

And suppose you wanted to sum the Num6 column. You’d select a cell in that column and use Alt+JT+T to add the totals row.

But Excel always sums the last column (that it’s able to) when it should really sum the one you’re on. That’s an easy enough fix

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lo As ListObject
        
    'There's a lot that can go wrong here, so we'll just ignore everything
    On Error Resume Next
    
    'Loop through every table on the sheet - but there should only be one IMO
    For Each lo In Me.ListObjects
        'If the range you're changing is exactly the same as the total row,
        'you're probably adding the total row. If there is no total row
        'you get an error - thus the on error above
        If Target.Address = lo.TotalsRowRange.Address Then
            'If you selected a cell in the table, that's the column you want to sum
            If Not Intersect(ActiveCell, lo.Range) Is Nothing Then
                lo.ListColumns(ActiveCell.Column - lo.DataBodyRange.Column + 1).TotalsCalculation = xlTotalsCalculationSum
            End If
            Exit For
        End If
    Next lo
    
End Sub

That won’t stop the last column from also being summed, but it’s easier to destroy than create. I’ll let that happen and go delete it if I don’t want it. But we’re not done yet. The craziness is just starting. By the way, I’m using Office 2010. If they fixed any of this stuff in later versions, bully for them, but I’m still not upgrading.

The formula to sum Num6 is: =SUBTOTAL(109,[Num6]). If I copy and paste that to Num5

The formula stays the same. That’s consistent with how copying formulas that use Structured Table Referencing (STR), but it really stinks in this case. If I want to sum all of the columns, I have to go select the appropriate aggregate function from the list. I could change the STR into normal cell references, but then I lose the table goodness.

One option is to select a cell in Num5 and press Alt+JT+T+Alt+JT+T. Turning the totals row off and then on again sums up for that column by calling the code I wrote above. The fact that this works means I should have check to see if there was already an aggregate in the cell before I overwrote it. That is, if I had a count formula in there and turned the total row off and then on again, I’d kill that row. If I fix that, this workaround doesn’t work. But it’s the right thing to do.

If IsEmpty(Intersect(lo.TotalsRowRange, ActiveCell.EntireColumn).Value) Then
                    lo.ListColumns(ActiveCell.Column - lo.DataBodyRange.Column + 1).TotalsCalculation = xlTotalsCalculationSum
                End If

Well that doesn’t work. If I have =SUBTOTAL(103,[Num5]) in the cell and turn on and off the total row, it changes to =SUBTOTAL(109,[Num5]).

I think what’s happening here is that Excel tables have a good memory. Once I change the aggregate to sum, that becomes the default for that column. When you show totals for the first time, the event sequence is (with the Target in parentheses)

1. Add the total row (A5:H5)
2. Add the total label (A5)
3. Aggregate the last column (H5)

When I re-show the total row after having changed the aggregate, the event code changes the default aggregate, and when the table restores the formula it’s using the that aggregate. Assume I’m in Num7 when I show totals the first time. Then I move to Num5, add a count, and hide and show totals.

The second-to-last change is Excel restoring the Num5 total from the last time. It doesn’t restore it to what it was, it restores it to whatever the last aggregate used was. Since I interjected some code between the adding of the row and the restoration of the aggregate, I changed the default for that column. At least I think that’s what happening.