Converting SUMs to SUBTOTALs

Everyone knows that SUBTOTAL ignores filtered rows. Readers of DDoE know that SUBTOTAL also ignores other SUBTOTAL formulas. I tell everyone who will listen about the benefits of SUBTOTAL. It’s one of the best received tips in the ‘Tips and Tricks’ portion of the training I do. But I still get spreadsheets that use SUM and individual adding of cells. When I do, I convert them to SUBTOTAL to make sure there are no errors. Today, I decided to automate that process.

I’ve filled column B over to the right into column C so I can preserve the original data.

With Excel’s color coding and this simple worksheet, you may have spotted the error in the grand total formula. Below is the code I wrote to correct this situation without having to put in all the SUBTOTALs manually.

Public Sub ConvertSumToSubtotal()
    
    Dim rCell As Range
    Dim rStart As Range
    
    Const sSUM As String = "=SUM("
    
    'Only work on ranges
    If TypeName(Selection) = "Range" Then
        'Only work on single columns
        If Selection.Columns.Count = 1 Then
            'rStart will adjust to be where ever the SUBTOTAL range will start
            Set rStart = Selection.Cells(1)
            'loop through the cells and replace SUM with SUBTOTAL
            'change rStart to point to cell just below the SUBTOTAL
            For Each rCell In Selection.Cells
                If rCell.HasFormula And Left(rCell.Formula, 5) = sSUM Then
                    rCell.Formula = "=SUBTOTAL(9," & rStart.Address(0, 0) & ":" & rCell.Offset(-1, 0).Address(0, 0) & ")"
                    Set rStart = rCell.Offset(1, 0)
                End If
            Next rCell
        End If
    End If
    
    'Make the last cell a SUBTOTAL of the whole range
    Selection.Cells(Selection.Rows.Count).Formula = "=SUBTOTAL(9," & Selection.Resize(Selection.Rows.Count - 1, 1).Address(0, 0) & ")"
    
End Sub

Public Sub ConvertSumToSubtotal()

Dim rCell As Range

Dim rStart As Range

Const sSUM As String = "=SUM("

'Only work on ranges

If TypeName(Selection) = "Range" Then

'Only work on single columns

If Selection.Columns.Count = 1 Then

'rStart will adjust to be where ever the SUBTOTAL range will start

Set rStart = Selection.Cells(1)

'loop through the cells and replace SUM with SUBTOTAL

'change rStart to point to cell just below the SUBTOTAL

For Each rCell In Selection.Cells

If rCell.HasFormula And Left(rCell.Formula, 5) = sSUM Then

rCell.Formula = "=SUBTOTAL(9," & rStart.Address(0, 0) & ":" & rCell.Offset(-1, 0).Address(0, 0) & ")"

Set rStart = rCell.Offset(1, 0)

End If

Next rCell

End If

'Make the last cell a SUBTOTAL of the whole range

Selection.Cells(Selection.Rows.Count).Formula = "=SUBTOTAL(9," & Selection.Resize(Selection.Rows.Count - 1, 1).Address(0, 0) & ")"

End Sub

This won’t work in every situation, but this layout is the one I see the most. This layout being SUMs for the subtotals and a big =A1+A2+A3 style formula for the grand total.

Once again SUBTOTAL saves the day and fixes the error. The most common error I see with this layout is in the grand total, but not always. Sometimes the subtotals don’t cover the correct range. It would seem easier when replacing the SUMs to use the same range the SUM uses, but I wanted to make sure I fixed any of those errors too. To do that, I SUBTOTAL from the cell below the previous SUBTOTAL to the cell above the current one.

Pro tip: Use

Ctrl+`

Ctrl+`

to toggle between viewing formulas and values (that’s an accent grave, left of the 1 key on US keyboards).

6 thoughts on “Converting SUMs to SUBTOTALs”

A note on your test for a single column. While putting together my code that filters by all selected values (http://yoursumbuddy.com/filter-column-by-all-selected-values/) I noticed that Selection.Columns.Count doesn’t always work.

That’s because it only catches the first area of the Selection. For instance if I select $A$1:$A$5,$B$7:$B$10, Selection.Columns.Count returns 1.

I came up with this test instead:

Union(ActiveCell.EntireColumn, Selection.EntireColumn).Address = ActiveCell.EntireColumn.Address

1	Union(ActiveCell.EntireColumn, Selection.EntireColumn).Address = ActiveCell.EntireColumn.Address

This works because Address encompasses all the areas of a range.

Good one Doug. I’ll probably adjust to check that Areas.Count = 1. I don’t need it to work on exotic layouts.

I used:

Sub M_snb()
  For Each cl In Columns(2).SpecialCells(-4123)
    If cl.Offset(-1) = "" Then
       cl.Offset(, 1) = "=subtotal(9," & Columns(2).SpecialCells(2, 1).Address & ")"
    Else
       cl.Offset(, 1) = "=subtotal(9," & Intersect(Columns(2), cl.Offset(-1).CurrentRegion).SpecialCells(2, 1).Address & ")"
    End If
  Next
End Sub