To sum only the rows a filter has left visible, use =SUBTOTAL(9,A2:A1001). A plain SUM adds everything in the range whether it’s filtered out or not, which is why your total doesn’t move when you filter. The first argument picks the calculation: 9 is SUM, 1 is AVERAGE, 2 is COUNT, 3 is COUNTA.
Use 109 instead of 9 if you also want to exclude rows you’ve hidden manually by right-clicking → Hide — for filtered rows the two behave identically, since SUBTOTAL always ignores those.
AGGREGATE does the same job with more control: =AGGREGATE(9,5,A2:A13), where 9 is SUM and 5 tells it to ignore hidden rows; option 7 ignores hidden rows and errors, which SUBTOTAL can’t do. To sum visible rows that also meet a condition, you need SUMPRODUCT with OFFSET, since SUMIF ignores filters entirely.
One shortcut worth knowing: Alt+= on a filtered range inserts SUBTOTAL automatically rather than SUM, and an Excel Table’s Total Row does the same.
Let’s say you have a list of weekly sales figures for different products in Excel. You’ve applied a filter to show only the sales for a specific product.
To find the total sales of this product, you would use a formula that can sum up only the visible cells showing the filtered results.
In Excel, to sum values only from the visible cells (that means when you have applied a filter), you need to use the SUBTOTAL function.
With this function, you can refer to the entire range, but the moment you apply a filter, it works dynamically and shows the sum only for the visible cells.
But apart from this, there are also more formulas and functions that you can use, and today we will explore all of them. In the above example, we have a list of values in the column, and I want to create a formula that can show me the sum whenever I apply a filter to the column.
Use SUBTOTAL to Sum Only Visible Cells
SUBTOTAL helps you quickly calculate totals like sums, averages, or counts, but only for visible rows after applying a filter. It’s useful when you want to ignore any hidden rows in your data.
You choose what kind of calculation you want (like sum or count) and specify a range to check for visible cells. Follow the steps below:
- First, in cell B1, enter the SUBTOTAL function.
- After that, in the first argument, enter 9 or 109 (both sum the values; the difference is explained below).
- Next, in the second argument, specify the range in column A where you have the numbers.
- In the end, enter the closing parenthesis and hit Enter.
In the following snapshot, I have not yet applied the filter, and it shows the sum of all the values that I have in the range.
Now to test this function you can add a filter and apply it. So, I’ll select 1, 2, and 3 in the filter.
And the moment I click OK, it shows the sum only for the filtered (visible) cells.
Now, this function is dynamic, and the moment you change the filter, it changes the result value according to the visible cells.
=SUBTOTAL(9,A2:A1001)
Both 9 and 109 leave out the rows hidden by a filter. The difference is with rows that you hide manually (right-click ➜ Hide): 9 still includes them, while 109 leaves them out as well. So, if you want the sum of only what you can see, use 109:
=SUBTOTAL(109,A2:A1001)
A quick way to get this formula is AutoSum. When you select the cell below a filtered list and press Alt + = (or click Home ➜ AutoSum), Excel enters a SUBTOTAL formula instead of SUM. And the Total Row of an Excel table also uses SUBTOTAL with 109.
Using AGGREGATE to Sum Visible Cells
AGGREGATE is a super function for calculating things like sums or averages. It can ignore hidden rows, errors, and even other subtotal calculations you’ve made, giving you clean results. You can pick the calculation type and tell it which cells to work with and what to ignore.
To sum only the visible cells, you can use the below formula:
=AGGREGATE(9, 5, A2:A13)
This formula sums values from the range A2:A13. The ‘9’ tells Excel that you want to sum the numbers. The ‘5’ means Excel will ignore hidden rows.
Like SUBTOTAL with 109, AGGREGATE with option 5 also ignores the rows that have been hidden manually. The real difference from SUBTOTAL is that AGGREGATE can also ignore errors: use option 7 to ignore both hidden rows and error values, so one #N/A in the range doesn’t break the total.
Sum Visible Cells with a Criterion
To sum visible cells based on specific criteria in Excel, you can use a formula that combines multiple functions like SUMPRODUCT, SUBTOTAL, ROW, MIN, and OFFSET. As SUMPRODUCT handles arrays by itself, you don’t need to press Ctrl + Shift + Enter.
=SUMPRODUCT(SUBTOTAL(109, OFFSET(A2, ROW(A2:A13) - MIN(ROW(A2:A13)), 0, 1)), --(A2:A13 > 100000))
This formula helps you add values from A2 to A13, but only in two cases: the cells must be visible (not hidden by a filter), and each cell’s value must be greater than 100,000.
- SUBTOTAL(109, OFFSET(…)) – This part checks each cell from A2 to A13 to see if it’s visible or not.
- OFFSET(…) – This goes to each cell in the range, one by one.
- ROW(A2:A13) – MIN(ROW(A2:A13)) – This calculates the position of each cell in the range to ensure the OFFSET works correctly.
- –(A2:A13 > 100000) – This checks if the cell’s value is more than 100,000, then turns the true/false result into 1 or 0.
- SUMPRODUCT(…) – This multiplies the visible cell values by the 1s and 0s of the condition and sums them up, so only the visible values of more than 100,000 are added.
If this formula feels too complex, there’s a simpler way with a helper column. In B2, enter the following formula and fill it down. It returns 1 when the row is visible and 0 when it’s hidden (refer to a column that never has blank cells, as SUBTOTAL 103 counts non-empty cells):
=SUBTOTAL(103,A2)
After that, use SUMIFS with the helper column as one more criterion:
=SUMIFS(A2:A13,B2:B13,1,A2:A13,">100000")
Frequently Asked Questions
SUM adds every cell in the range, whether it’s visible or not. Only SUBTOTAL and AGGREGATE check if a row is hidden, so use one of them for filtered data.
No. SUBTOTAL and AGGREGATE only skip hidden rows. If you sum a range across a row with hidden columns, the hidden values are still included.
How do I copy only the visible cells?
Select the range, press Alt + ; (semicolon) to select only the visible cells, and then copy it. With filtered data, Excel already copies only the visible rows.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.