Best Practices to Sum Column with Parents & Children?

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:

  1. Indent all of the cost codes once more, and add a row at the top of the table that sums all descendant cells.

  2. 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 so much!

One approach to this might be to check whether a row has no parent or child rows, and if so, show a warning.

For, example, the following column formula will show “OK” if a row has a parent or a child, but “WARNING” if not:

IF(PARENTCELL(A),"OK",IF(ISBLANK(DESCENDANTCELLS(A)),"WARNING","OK"))

In action, a row added to the bottom with no child or parent would appear as:

You then might add conditional formatting to make these rows stand out by highlighting them in red:

Hope this helps.

1 Like

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?

Thanks!

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. :

Define the aggregate formula in header region using AGGREGATE/SUBTOTAL and also each parent cell aggregate formulas as AGGREGATE/SUBTOTAL.

2 Likes

NOTE: Looks like @msiraj and I were writing a response to this at the same time! Superior solution by him above :point_up_2:, 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

I would define this as follows:

IF(PARENTCELL(A),"NO",IF(ISBLANK(DESCENDANTCELLS(A)),"NO","YES"))


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.

For example, for column E I would write:

=SUMIF(C5:C,"YES",E5:E)

This would result in a sum of only top-level parent row values of column E:

1 Like

You two are amazing. I am mostly grabbing from @msiraj’s suggestions but using the best of both worlds. Stay tuned. This is really cooking!

2 Likes

This thread should be part of spreadsheet main tutorial / academy. It’s very useful!

1 Like