Sum Only Visible Cells (Filter Cells)

HomeExcel FormulasSum Only Visible Cells (Filter Cells)

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.

list-of-values

Use SUBTOTAL to Sum Only Filter Cells

  1. First, in cell B1 enter the SUBTOTAL function.
  2. After that, in the first argument, enter 9, or 109.
  3. Next, in the second argument, specify the range in column A, where you have the number.
  4. In the end, enter the closing parentheses and hit enter.
subtotal-to-sum

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.

subtotal-result

Now to test this function you can add a filter and apply it. So, I’ll select 1,2, and 3 in the filter.

add-a-filter

And the moment I hit enter it, shows the sum only for the filtered cells (visible).

sum-of-filtered-values

Now, this function is dynamic and the moment you change the filter it changes the result value according to the visible cells.

dynamic-subtotal-function

=SUBTOTAL(9,A2:A1001)

🔙 Excel Formulas