You apply a filter to your Excel data, but the SUM function keeps calculating hidden rows. This happens because the standard SUM function includes all cells in its range, regardless of visibility. Excel provides a dedicated function, SUBTOTAL, designed to perform calculations on filtered data. This article explains how to use SUBTOTAL to sum only visible cells and clarifies when to use it instead of SUM.
Key Takeaways: Summing Filtered Data in Excel
- SUBTOTAL function with 109: Calculates a sum that automatically ignores rows hidden by a filter.
- AutoSum button after filtering: Inserts the SUBTOTAL function automatically when you sum a filtered column.
- SUM function: Adds all numbers in a range, including those in rows hidden by filtering.
Why SUM Doesn’t Work on Filtered Data
The SUM function is a basic arithmetic tool. It adds all numerical values you specify within its range. It does not evaluate the state of the worksheet. When you hide rows using a filter, Excel merely changes their display property. The data in those rows remains part of the worksheet and, therefore, part of any range referenced by SUM. This is why a SUM formula continues to show the total for all data, giving incorrect results for your filtered view. To perform calculations that respond to filtering, you need a function that can ignore hidden rows.
Steps to Sum Visible Cells with SUBTOTAL
The SUBTOTAL function is the correct tool for this task. It uses a function number to specify the type of calculation, and by default, it excludes values in rows hidden by a filter.
Method 1: Using the SUBTOTAL Function Directly
- Select the cell for your total
Click on the cell where you want the sum of visible rows to appear, typically below your filtered data. - Type the SUBTOTAL formula
Type =SUBTOTAL( and then enter the function number. For a sum that ignores filtered rows, use 109. The formula should look like this: =SUBTOTAL(109, - Select your range
With your mouse, select the range of cells you want to sum, such as B2:B100. The formula will now read =SUBTOTAL(109,B2:B100). - Complete the formula
Type a closing parenthesis ) and press Enter. The cell will now display a sum that updates automatically when you change the filter.
Method 2: Using the AutoSum Button on a Filtered Column
- Apply your filter
Select your data range and click Data > Filter to enable filtering. Use the dropdown arrows to filter your data as needed. - Select the cell below the visible data
Click on the empty cell directly beneath the column of numbers you want to sum. - Click the AutoSum button
Go to the Home tab on the ribbon. In the Editing group, click the AutoSum button (the Greek sigma icon Σ). - Confirm the formula
Excel will automatically insert a SUBTOTAL function, not a SUM function, and highlight the range of visible cells. Press Enter to confirm.
Common Mistakes and Limitations
Using the Wrong SUBTOTAL Function Number
SUBTOTAL has two sets of function numbers. Numbers 1-11 include values in hidden rows. Numbers 101-111 ignore values in hidden rows. For summing filtered lists, always use 109 or 9. Using 9 will work but is from the older set that includes manually hidden rows. For consistency with filters, 109 is the best choice.
SUBTOTAL Ignores Other SUBTOTALs
A key design of the SUBTOTAL function is to avoid double-counting. If your selected range contains other cells with SUBTOTAL formulas, those cells are automatically excluded from the new SUBTOTAL calculation. This is intentional and prevents totals from including other totals.
Manually Hidden Rows vs. Filtered Rows
The SUBTOTAL function with argument 109 ignores rows hidden by a filter. However, if you right-click a row and select Hide, that row is manually hidden. SUBTOTAL with 109 will still include values from manually hidden rows. Only the older function numbers (1-11) ignore manually hidden rows, but they include filtered rows.
SUBTOTAL vs SUM: Key Differences
| Item | SUBTOTAL | SUM |
|---|---|---|
| Primary Use | Calculations on filtered or subtotaled lists | Basic addition of all numbers in a range |
| Response to Filter | Automatically ignores rows hidden by a filter | Includes all rows, regardless of filter |
| Function Numbers | Uses numbers (e.g., 109 for sum) to specify calculation type | No function numbers; only one operation |
| Nesting Behavior | Ignores other SUBTOTAL cells within its range | Will sum every cell, including other SUM formulas |
| Manual Row Hiding | Argument 109 includes manually hidden rows | Includes manually hidden rows |
You can now use the SUBTOTAL function to create accurate totals for your filtered reports. Remember to use function number 109 for a sum that respects your active filters. For your next task, try using SUBTOTAL with other function numbers like 101 for AVERAGE or 103 for COUNTA on filtered data. A useful advanced tip is to name your data range as a Table using Ctrl+T; this allows your SUBTOTAL formulas to automatically expand when you add new rows of data.