Author: Oscar Cronquist Article last updated on August 06, 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:


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 right click on a row number and then click on "Hide" to hide the entire row. Select the rows around a hidden row and then right click on them to open a menu, there click 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, click on "Filter" button. A black down-pointing arrow appears next to header name "Numbers", click 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 click 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.

Download excel *.xlsx file

Sum only visible cells.xlsx