SUBTOTAL Examples

Summing a column of numeric values is common practice. It is easy with an Excel Table as the option to add a Total Row is given in the Table Design contextual tab.

Ticking this checkbox adds ‘Total’ to the leftmost bottom cell and generates a SUBTOTAL formula in the rightmost. Despite the name, this function does a lot more than summation.

Page 1 reveals the myriad of options its function_num argument offers.

Columns with purely numeric values default to a function_num of 109 – SUM, whereas everything else triggers 103 – COUNTA (counts non-empty cells).

You’d be forgiven for thinking the second half is indistinguishable from the first. Aside from the ’10’ and ‘1’ prefixes, there is a subtle difference between them.

What is the difference?

The second set don’t calculate values in hidden rows, whereas the first do. Both ignore unfiltered values though.

Page 2 and 3 show the effect of using 109 – SUM and 9 – SUM, respectively.

If you want hidden and unfiltered values to be included in a total, use SUM (page 4).

Why is this important?

Awareness of these differences is important so you know exactly what the formula is doing to return the result it does. This is particularly important for shared workbooks, which may contain hidden rows unbeknownst to you.