We are building a budget in Spreadsheet based on the template Construction Budget. One thing we are trying to do with Spreadsheet is eliminate all possible paths for human error that could have occurred in Excel.
For instance, in Excel, one could Sum a column range, and later add another row to the bottom of the column and completely forget that the summed range didn’t update to reflect the new row!
To help avoid this, and with the Construction Budget in mind, what would you advise for best practices. As I see it there are two possible solutions:
Indent all of the cost codes once more, and add a row at the top of the table that sums all descendant cells.
Add a row at the bottom that sums all parent sums above.
Can you advise what you think is the least likely to cause human error? Also, what would the correct formulas be to achieve either 1 or 2?
Thanks for this, Matt. That’s very helpful for catching missed rows.
I’m afraid I’m still a little hazy on the best practices for summing a column with parents and children. My parents are all using SUM(DESCENDANTCELLS().
Should I be summing from the bottom of the column or the top? And, what’s the best formula to make sure I’m not double counting my subtotals already in the parent rows?
As a good practice have all aggregate functions which work on entire column (table-region data) defined in the header region as this is not affected by view sort/filter formula adjustments.
Eg: Total Budget: =AGGREGATE(9,0,F5:F)
Also notice, having unbounded range-end for budget column i.e. F5:F which selects all cells in column F from row-index 5 ( i.e. start of table region ) to the last cell in that column.
Additionally to ensure, sub-totals are not considered in the aggregate results, consider using the following aggregate function alternatives instead of SUM, AVERAGE etc. :
NOTE: Looks like @msiraj and I were writing a response to this at the same time! Superior solution by him above , defer to his guidance (he is the lead developer on all things formula-related here at Spreadsheet.com).
Here’s what I would do, with the caveat that there may be a simpler/cleaner solution to this. This solution assumes that you have formulas to rollup data into your parent rows already, as is done in the Construction Budget template.
1. Define a column formula to determine whether each row is a top-level parent
2. For each column you want to sum, write a formula above the header row to sum only top-level parent row values in that column
Above the table header row, for each column you want to sum, I would write formula using the SUMIF function to only sum top-level parent rows. Using ranges that end in just the column letter, such as C5:C, will always include newly added rows so you shouldn’t have to worry about newly inserted rows being missed.
, defer to his guidance (he is the lead developer on all things formula-related here at 
