sum of parent rows only, below parent and child rows

Seanfo

Itemized budget rows, which are the sum of child rows. All data entered in child rows and totaled in the parent row.

At bottom of page, I want to total the parent rows only.

Any way to do this?

Screen grab attached.

Image 10-3-22 at 2.40 PM.jpg

Cleversheet

@Seanfo, it's really quite easy. You only need two simple formulas in each applicable column: =SUM(CHILDREN(() on the three summing rows (the two parents and their common grandparent) and =[SameColumn]$1 (assuming the grandparent of your two groups is on row 1) below the bottom data row.

Cleversheet

Is it crucial to have the result appear at the bottom? You could have them appear so easily at the top by simply nesting those two 00000 items under a row above them, and applying =SUM(CHILDREN()) in the applicable columns. In other words, the sum of the sums shows up that way; only the children are summed, not the grandchildren yet again. That is, the result on the new top line is $2, not $4.

Seanfo

Yes, having it on top is easy and i was hoping there would be a formula which would work in order to keep it below. I have several budget templates used for construction and it just makes more sense to have the totals at the bottom for us. Thanks for your comment!

Cleversheet

The only thing I can think of is to go ahead and create a grandparent layer on top, but use =[selected cell] on a row at the bottom, spread across the row, to grab the grandparent value.

Thus, if the grandparent is on row 1, and the parents are on rows 2 and 8, then your subtotals on row 15 in columns 3, 4 and 5 would be =[Column3]1, =[Column5]1, =[Column5]1. Depending on the use case you might need to add $ signs before the row numbers.

If you didn't want row 1 to be visible you could create a sheet filter or use a report filter to hide that row.

Would be nice, of course, if Smartsheet made this easier with a function.

Seanfo

This is a great idea, and actually works for one of my templates. I tried it and it works well - I can add or delete rows without the need for editing the sum formula in a row that resides below as opposed to above.

I have other templates which have divisional sections.. This means there is a grandparent row with parent and child rows. Then there is a spacer row (blank) to separate divisions.

I was hoping that since the hierarchal rows are all "indents" ex: Indent 0, Indent 1, Indent 2 etc, that i could use a formula to sum all "Indent 1 row cells in any column. I couldnt figure that out however.

Thanks for your input!

Cleversheet

Assuming you want to sum the values of all the “Indent 1” rows in the sheet, you could create a [Row Fmt] column with the formula =COUNT(ANCESTORS()) to distinguish those rows from all others, then wherever you want the result to appear use a SUMIFS formula to grab and sum only the values in those rows (ie, give me the sum of values in [Whatever] column where [Row Fmt] = 1).

Seanfo

Coming back to this after a long pause! This seems like the solution. I just don't know how to write the formula!

Cleversheet

@Seanfo, it's really quite easy. You only need two simple formulas in each applicable column: =SUM(CHILDREN(() on the three summing rows (the two parents and their common grandparent) and =[SameColumn]$1 (assuming the grandparent of your two groups is on row 1) below the bottom data row.

Seanfo

This solved it. Used the grandparent row at top of sheet and sum of children. Totals row toward bottom of sheet equals that of the grandparent row. My fees then calculate below that. Did not have to use a SUMIFS formula with that layout.

Highlighted the top row to remind staff not to print when exporting for sharing.

Made this action part of several templates and works perfectly.

Thanks for your help on this it is much appreciated!

:-)