1. Problem Situation
In real-world data handling, it is often necessary to apply filtering to a large data table based on specific criteria (e.g., specific period, particular department) and then calculate the sum of only the cells visible on the screen. If you use the standard SUM function (e.g., =SUM(A1:A10)), it includes the values from all rows, even those temporarily hidden by the filter. This inconsistency with the desired ‘sum of filtered results’ leads to inefficiency, requiring users to either unfilter the data or manually redefine the range to get an accurate sum. This is a major cause of decreased accuracy and speed in business data analysis.
2. Solution
To overcome this issue and accurately calculate the sum of filtered data, you should use the SUBTOTAL function. The SUBTOTAL function allows you to set an argument that ignores hidden rows and only aggregates the values in the displayed cells.
Function Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)- function_num: A number specifying the type of aggregation to perform. To calculate the sum of filtered data, you must use 109. (Numbers 1-11 include hidden rows, while 101-111 aggregate only visible cells, excluding hidden rows. 9 means SUM, and 109 means Visible SUM.)
- ref1: The range of cells to be summed.
Step-by-Step Application
- Select the cell where the total sum should be displayed.
- Enter the following formula, specifying the range where your data is located (e.g., C2:C6).
- =SUBTOTAL(109, C2:C6)
- Apply a filter to your data and perform the desired filtering.
- The cell containing the SUBTOTAL function will automatically display only the sum of the visible cells according to the filter criteria.
3. Example Data Table
The table below demonstrates how the SUM and SUBTOTAL functions behave differently when data is filtered.
| Category (Column A) | Value (Column B) |
| ItemA | 10 |
| ItemB (Hidden) | 20 |
| ItemC | 30 |
| ItemD (Hidden) | 40 |
| ItemE | 50 |
| Formula | Description | Result (When Items B, D are hidden) |
| =SUM(B2:B6) | Total sum including hidden rows | 150 |
| =SUBTOTAL(109, B2:B6) | Sum including only visible cells | 90 (10 + 30 + 50) |
4. Summary
Utilizing the SUBTOTAL function with function_num set to 109 is an essential technique for filtered data analysis. This method allows you to automatically calculate accurate partial sums even after complex filtering operations, which significantly reduces data analysis time and improves accuracy.