[Excel VBA] How to Aggregate Only Filtered Ranges | Comparing Subtotal and Sum Functions

Overview

When you want to aggregate only the visible cells extracted by an AutoFilter, WorksheetFunction.Subtotal is the ideal choice. This article provides sample code to retrieve both the total for the filtered range and the total for the entire worksheet simultaneously, while explaining the differences between the Subtotal and Sum functions.

Prerequisites

Sample Code (VBA)

The following code calculates the totals for both the visible filtered range and the entire column range.

Sub CalculateFilteredTotals()

    Dim visibleTotal As Double      ' Total of the filtered range
    Dim overallTotal As Double      ' Total of the entire range
    Dim tgtColumn    As Range       ' Target column
    
    ' Set Column E as the target column
    Set tgtColumn = ActiveSheet.Columns("E")
    
    ' 9 = Function code representing SUM
    visibleTotal = WorksheetFunction.Subtotal(9, tgtColumn)
    
    ' Total ignoring the filter (standard Sum)
    overallTotal = WorksheetFunction.Sum(tgtColumn)
    
    MsgBox "Filtered Total: " & visibleTotal & vbCrLf & _
           "Overall Total: " & overallTotal, _
           vbInformation, "Comparison of Totals"

End Sub

Key Points of the Code

Comparison: Subtotal vs. Sum

Application Examples

• To get the row count: Use function code 3 (COUNTA).
• To get the average: Use function code 1 (AVERAGE).
• To output results to a cell: Transfer the value directly using a command like Range("H2").Value = visibleTotal.

Frequently Asked Questions

Q: Can Subtotal be used for non-numeric columns?

A: Yes, it can be used if you specify a counting function code (such as COUNT or COUNTA).

Q: Can the same code be used for Excel Tables (ListObjects)?

A: Yes. It works similarly if you specify the target range using syntax like ListObject.DataBodyRange.Columns(“Sales”).

Summary

The Subtotal function is extremely useful when you need to obtain the sum of only the extraction results, as it automatically excludes filtered or manually hidden rows. Conversely, the Sum function targets the entire sheet. Using both in combination to verify differences can help improve the accuracy of your reports.