SUBTOTAL

Calculates a subtotal for a list, ignoring filtered-out rows.

The SUBTOTAL function is specifically designed for vertical ranges of data and is the standard for aggregate calculations in Excel Tables and filtered lists. Its primary advantage is its ability to automatically exclude rows hidden by a filter, ensuring the result reflects only what is visible to the user. Additionally, SUBTOTAL is programmed to ignore any other SUBTOTAL formulas within the reference range, effectively preventing double-counting in multi-layered reports. The $functio{n}_n$um argument (1-11 or 101-111) determines the type of calculation and whether manually hidden rows (those hidden via right-click > Hide) are included. Constants 1-11 include manually hidden rows but exclude filtered-out rows, whereas 101-111 exclude both manually hidden and filtered-out rows. It is important to note that SUBTOTAL only reacts to hidden rows, not hidden columns. For modern workflows, using the 100-series codes (such as 109 for SUM) is considered best practice to ensure results remain consistent regardless of how data was hidden.