One of the most frustrating experiences in Microsoft Excel occurs when you use the standard SUM function to add up a column of numbers, and then use a filter to hide half of the rows. Instead of updating to show the total of only the visible rows, the SUM function stubbornly continues to count the hidden data, providing a completely inaccurate total for what you are currently looking at.
To fix this, you must abandon the SUM function and use its much smarter, more flexible counterpart: the SUBTOTAL function.
Understanding the SUBTOTAL Function
Unlike SUM, AVERAGE, or COUNT, the SUBTOTAL function is a chameleon. It can perform 11 different mathematical operations, and it has a unique, built-in ability to completely ignore any rows that have been hidden by a filter or hidden manually.
The syntax looks like this:
=SUBTOTAL(function_num, ref1)
- function_num: A specific code number that tells Excel what kind of math to do (e.g., 9 means SUM, 1 means AVERAGE).
- ref1: The range of cells you want to calculate.
How to Sum Only Visible Rows
Assume you have a column of sales figures from cell B2 to B50. You want to add them together, but you want the total to dynamically update when you filter the list to show only a specific region.
- Click on the cell at the bottom of the column where you want the total to appear.
- Type the following formula:
=SUBTOTAL(9, B2:B50) - Press Enter.
The number 9 is the internal code for “SUM”. Right now, the formula will display the exact same total as a standard SUM(B2:B50) formula.
However, if you apply a Data Filter to the spreadsheet and hide 20 rows, the SUBTOTAL formula will instantly recalculate, dropping the hidden rows from its memory and displaying the perfect total for the remaining 30 visible rows.
The Difference Between Filtered and Manually Hidden Rows
There is a crucial technical distinction in Excel between hiding rows using a Data Filter and hiding rows Manually (by right-clicking a row number and selecting “Hide”).
- Code
9will ignore rows hidden by a Filter, but it will still count rows that you manually hid by right-clicking. - If you want the formula to ignore absolutely everything you cannot see—both filtered and manually hidden rows—you must use a different code number.
To completely ignore all hidden rows, regardless of how they were hidden, you add 100 to the function code.
Instead of 9, use 109.
=SUBTOTAL(109, B2:B50)
Other Useful SUBTOTAL Codes
Because SUBTOTAL is a multi-tool, you can use it for far more than just addition. Here are the most common function codes. Remember to add 100 to any of these numbers (e.g., 101, 102) if you need them to ignore manually hidden rows.
- 1: AVERAGE (Calculates the average of the visible cells)
- 2: COUNT (Counts how many visible cells contain numbers)
- 3: COUNTA (Counts how many visible cells contain any text or data)
- 4: MAX (Finds the highest number among the visible cells)
- 5: MIN (Finds the lowest number among the visible cells)
By replacing your standard math formulas with SUBTOTAL, you guarantee that your dashboard totals always perfectly match the data that is actually visible on the screen.