How to Sum Visible Cells (Filtered Cells) in Excel Using the SUBTOTAL Function

How to Sum Visible Cells (Filtered Cells) in Excel Using the SUBTOTAL Function. If you want to sum only the visible cells in Excel (i.e., when a filter is applied), you can use the SUBTOTAL function. This function allows you to reference the entire range, but when you apply a filter, it works dynamically to show the sum of only the visible cells.

To demonstrate, consider the following example where we have a list of values in a column, and we want to create a formula that will sum only the visible cells when a filter is applied to the column.

How to Sum Visible Cells (Filtered Cells) in Excel

To achieve this, follow these steps:

1. Enter the SUBTOTAL function in cell B1.

2. In the first argument of the function, enter 9 or 109.

3. In the second argument, specify the range of cells in column A where you have the numbers.

4. Close the parentheses and hit enter.

The result will show the sum of all values in the range.

Now, to test this function, you can add a filter to the column. In the example below, we selected 1, 2, and 3 in the filter.

When you hit enter, the result will show the sum only for the filtered cells.

The SUBTOTAL function is dynamic, which means it changes the result value according to the when you change the filter. You can use the following formula to sum only visible cells in Excel using the SUBTOTAL function:

=SUBTOTAL(9,A2:A1001)