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 𝚏𝚞𝚗𝚌𝚝𝚒𝚘𝚗_𝚗𝚞𝚖 argument offers.
Columns with purely numeric values default to a 𝚏𝚞𝚗𝚌𝚝𝚒𝚘𝚗_𝚗𝚞𝚖 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.
𝗪𝗵𝗮𝘁 𝗶𝘀 𝘁𝗵𝗲 𝗱𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝗰𝗲?
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).
𝗪𝗵𝘆 𝗶𝘀 𝘁𝗵𝗶𝘀 𝗶𝗺𝗽𝗼𝗿𝘁𝗮𝗻𝘁?
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.
