Summing Only Visible Cells with SUBTOTAL in Excel. To sum values exclusively from visible cells in Excel, particularly when a filter is applied, employ the dynamic SUBTOTAL function. This function adapts dynamically to the filtered view, displaying the sum solely for the visible cells.
Summing Only Visible Cells with SUBTOTAL in Excel
Summing Only Visible Cells with SUBTOTAL in Excel. In the provided example featuring a column of values, the objective is to create a formula that yields the sum whenever a filter is applied to the column.
- Use SUBTOTAL to Sum Only Filtered Cells:
- In cell B1, input the SUBTOTAL function.
- In the first argument, enter either 9 or 109.
- Specify the range in column A, where the numbers are located, in the second argument.
- Complete the function with a closing parenthesis and press enter.
In the initial state without a filter, the function displays the sum of all values in the range.
To test the function, apply a filter. For instance, select 1, 2, and 3 in the filter.
Upon hitting enter, the function reveals the sum solely for the filtered cells, demonstrating its dynamic nature.
The SUBTOTAL function remains dynamic, adjusting the result value based on changes in the filter. The formula, as shown in the last snapshot, captures this dynamism:
=SUBTOTAL(9,A2:A1001)
