Often when performing a grand total of many subtotals, the subtotal rows are added together individually. This technique creates the risk of the grand total being incorrect when new rows or subtotals are inserted.
In Excel 2010 and 2013 a new function was added called AGGREGATE, which will add all rows and not include prior subtotals.
To use this effectively, each subtotal line will use the AGGREGATE function, including the final Grand Total. This Grand Total will include the very first line of all the rows and the last line, and will ignore all the lines (subtotals) where AGGREGATE is found.
It’s important to not use a SUM on any of the subtotals as they will be double counted. This technique will also only work with values that are the same sign, so you can’t add income and expenses this way.
The second column B shows formulas (Ribbon-Formulas/Show Formulas)
In the AGGREGATE Function the 9 represents Sum, and the 0 will ignore other subtotals and aggregate functions. The last part of the function is the range to be added.
The 0 choice works well with financial reports due to the nature of hiding rows or suppressing rows that are all zero.
Notice how the first two subtotals add the respective groups (Compensation and Office & Building). The TOTAL OPERATING adds all previous rows, and the TOTAL EXPENSES row adds from Salary to Interest Expense.
Here is a list of the various options.
