Author: Oscar Cronquist Article last updated on November 15, 2018


The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture above shows a cell range that has row 4 and 9 hidden. The usual SUM function won't work in this case, you need the SUBTOTAL function:

=SUBTOTAL(109,C3:C12)

The first argument allows you to pick a function number that determines how the SUBTOTAL function behaves. In this case, 109 sums all visible cells in a cell range.

To hide a value simply press with right mouse button on on a row number and then press with left mouse button on "Hide" to hide the entire row. Select the rows around a hidden row and then press with right mouse button on on them to open a menu, there press with left mouse button on "Unhide" to show the value again.

The picture above shows filtered values in column C. Excel tells you that the cell range is filtered by the color of the row numbers and the icon next to "Numbers" in cell C2.

To apply a filter to a column simply select the cell range, go to tab "Data" on the ribbon, press with left mouse button on "Filter" button. A black down-pointing arrow appears next to header name "Numbers", press with left mouse button on it to apply a filter.

The Excel defined table above has a built-in feature that allows you to sum filtered values automatically, all you need to do is select a cell in the table, go to tab "Design" on the ribbon, then press with left mouse button on check-box "Total Row" to show the totals.

Cell C13 in the picture above displays the total for filtered cells. The SUBTOTAL function works just as well if you prefer using an Excel function with an Excel defined table, demonstrated in cell C15.

Get excel *.xlsx file

Sum only visible cells.xlsx