The picture above shows row 4 and row 8 hidden, the SUM argument 9 sums all values in C3:C11 whereas argument 109 ignores hidden values. That is why you see different sums in cell B14 and B15.

Excel function syntax

SUBTOTAL(function_num, ref1, ...)

The function returns a subtotal from a list or database, you can choose from a variety of arguments that determine what you want the function to do.

The SUBTOTAL function allows you to have up to 254 cell ranges.



Other SUBTOTAL formulas in your cell range are ignored to avoid double counting.

The SUBTOTAL function also ignores hidden values created by a filter no matter which argument you use.

There is a filter applied to cell range B3:C11 in the picture above.

Download excel *.xlsx

SUBOTAL function.xlsx