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

2025年12月27日

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

Item	Details
Excel Version	Microsoft 365 or 2016 or later
Data Column	Column E (Assuming numeric values such as sales amounts)
Macro Location	Standard Module

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

Row	Description
9	`Subtotal(9, TargetRange)` is used to retrieve the sum of only the visible cells after filtering.
12	The `Sum` function aggregates the entire column regardless of whether a filter is applied.
14	`MsgBox` displays both the filtered total and the overall total side-by-side to confirm the difference at a glance.

Comparison: Subtotal vs. Sum

Item	Subtotal (9)	Sum
Manually Hidden Rows	Ignored	Included in aggregation
Rows Hidden by Filter	Ignored	Included in aggregation
Formula Support	Available	Available
Primary Use Case	Aggregating after filtering	Aggregating the entire dataset

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.

よかったらシェアしてね！