To sum values only from the visible cells in Excel (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 show the sum only for the visible cells.
In the following 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 Filter Cells
- First, in cell B1 enter the SUBTOTAL function.
- After that, in the first argument, enter 9, or 109.
- Next, in the second argument, specify the range in column A, where you have the number.
- In the end, enter the closing parentheses 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 hit enter it, shows the sum only for the filtered cells (visible).
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)