Counting and Summing Functions – Daily Dose of Excel

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).

Sometimes I just want to quickly see the difference between two cells or groups of cells. Excel puts some great aggregates in the status bar.

and you can even customize them. Right click on the those aggregates.

But I wanted the difference. So I wrote some code to find it. I already had a class module with an Application object declared WithEvents, so I added this SheetSelectionChange event procedure.

Private Sub mxlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    If TypeName(Selection) = "Range" Then
        ShowDifferenceStatus Selection
    End If

End Sub

Private Sub mxlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If TypeName(Selection) = "Range" Then

ShowDifferenceStatus Selection

End If

End Sub

That event procedure calls this procedure in a standard module.

Public Sub ShowDifferenceStatus(rSel As Range)
        
    Dim wf As WorksheetFunction
    Dim vStatus As Variant
    
    On Error Resume Next

    Set wf = Application.WorksheetFunction
    
    If rSel.Areas.Count = 1 Then
        If rSel.Columns.Count = 2 Then
            vStatus = "Difference: " & Format(wf.Sum(rSel.Columns(1)) - wf.Sum(rSel.Columns(2)), "#,##0.00")
        ElseIf rSel.Rows.Count = 2 Then
            vStatus = "Difference: " & Format(wf.Sum(rSel.Rows(1)) - wf.Sum(rSel.Rows(2)), "#,##0.00")
        Else
            vStatus = False
        End If
    ElseIf rSel.Areas.Count = 2 Then
        If (rSel.Areas(1).Columns.Count = 1 And rSel.Areas(2).Columns.Count = 1) Or _
            (rSel.Areas(1).Rows.Count = 1 And rSel.Areas(2).Rows.Count = 1) Then
            
            vStatus = "Difference: " & Format(wf.Sum(rSel.Areas(1)) - wf.Sum(rSel.Areas(2)), "#,##0.00")
        End If
    Else
        vStatus = False
    End If
    
    Application.StatusBar = vStatus
    
End Sub

Public Sub ShowDifferenceStatus(rSel As Range)

Dim wf As WorksheetFunction

Dim vStatus As Variant

On Error Resume Next

Set wf = Application.WorksheetFunction

If rSel.Areas.Count = 1 Then

If rSel.Columns.Count = 2 Then

vStatus = "Difference: " & Format(wf.Sum(rSel.Columns(1)) - wf.Sum(rSel.Columns(2)), "#,##0.00")

ElseIf rSel.Rows.Count = 2 Then

vStatus = "Difference: " & Format(wf.Sum(rSel.Rows(1)) - wf.Sum(rSel.Rows(2)), "#,##0.00")

Else

vStatus = False

End If

ElseIf rSel.Areas.Count = 2 Then

If (rSel.Areas(1).Columns.Count = 1 And rSel.Areas(2).Columns.Count = 1) Or _

(rSel.Areas(1).Rows.Count = 1 And rSel.Areas(2).Rows.Count = 1) Then

vStatus = "Difference: " & Format(wf.Sum(rSel.Areas(1)) - wf.Sum(rSel.Areas(2)), "#,##0.00")

End If

Else

vStatus = False

End If

Application.StatusBar = vStatus

End Sub

If the selection is contiguous (Areas.Count = 1), it determines if there are two columns or two rows. Then it uses the SUM worksheet function to sum up the first and subtract the sum of the second. Anything other that two columns tow rows resets the StatusBar by setting it to False. Subtracting one cell from the other is easy enough, but I wanted the ability to subtract one column from the other (or one row). Using SUM also avoids me having to check for text or other nonsense that SUM does automatically. Here’s one where I only have one Area selected and it contains two columns. It sums the numbers in column B and subtracts the sum of column C.

When the selection is not contiguous (Areas.Count = 2), then it determines if both areas have only one column or only one row. If either has more than one, it resets the status bar. But if they both have one (of either), it subtracts them. Here I’ve selected B2:B3, then held down the Control key while I selected C3:C4. That’s two areas, but each only has one column, so it assumes I want to subtract columns.

The next feature I want to add is to recognize filtered data. Often I’m working with a filtered Table and although two cells appear to be adjacent, selecting them without holding down Control really selects all those filtered cells in between. I guess I’ll need to loop through and determine what’s visible, build a range from only those cells, and sum that. For now, I’m just holding down control and using the mouse to select them. If you’re not familiar, the “mouse” is that blob of plastic several inches away from home row (aka the productivity killer). Excuse me while I get off my soap box and finish this post.

I tried to glean the NumberFormat of the cells selected and use that in the display. You can see from the code above that I punted and just used a comma and two decimals. But that stinks for really small numbers. Originally, I had something like

vStatus = "Difference: " & Format(wf.Sum(rSel.Columns(1)) - wf.Sum(rSel.Columns(2)), rSel.Cells(1).NumberFormat)

1	vStatus = "Difference: " & Format(wf.Sum(rSel.Columns(1)) - wf.Sum(rSel.Columns(2)), rSel.Cells(1).NumberFormat)

But look at the craziness when the cell as the Accounting format (_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_))

It works well for times though.

Apparently the syntax for cell formatting is slightly different than for the VBA.Format function. I haven’t worked out what the differences are, but maybe someday I will.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Category: Counting and Summing Functions

Converting SUMs to SUBTOTALs

Subtracting Cells in the Status Bar