How to Sum Visible Cells Only (Filter Cells) in Excel
How to Sum Visible Cells Only (Filter Cells) in Excel. When you apply a filter in Excel and want to sum values only from the visible cells, you can use the SUBTOTAL function. This function allows you to refer to the entire range, but it dynamically calculates the sum for the visible cells only.
Consider the following example where we have a list of values in a column, and we want to create a formula that shows the sum of the visible cells whenever we apply a filter to that column.
How to Sum Visible Cells Only (Filter Cells) in Excel
How to Sum Visible Cells Only (Filter Cells) in Excel
To use the SUBTOTAL function, follow these steps:
- In cell B1, enter the SUBTOTAL function.
- In the first argument, enter either 9 or 109.
- In the second argument, specify the range in column A where you have the numbers.
- Enter the closing parentheses and press Enter.
How to Sum Visible Cells Only (Filter Cells) in Excel
At this point, the function displays the sum of all the values in the range, regardless of whether they are visible or not.
How to Sum Visible Cells Only (Filter Cells) in Excel
To test the function, you can apply a filter to the column. For instance, you can select values 1, 2, and 3 in the filter. The function will then update to show the sum of the visible cells only.
How to Sum Visible Cells Only (Filter Cells) in Excel
The beauty of this function is that it’s dynamic, which means that if you change the filter, it automatically recalculates the sum based on the visible cells only.
How to Sum Visible Cells Only (Filter Cells) in Excel
To sum up, you can use the following formula to sum only visible cells when filtering in Excel:
=SUBTOTAL(9,A2:A1001)
