Sum of Parents

jenwillcockson

I keep track of pending transactions in a real estate office, and have each month as a parent and the closings scheduled as children under the correct month.  On the parent row of each month I've got totals showing for the children.  This all works great.

What I can't figure out is how to total the parents. At the bottom of the sheet I have a total row.  I need this row to show the total of all pending transactions.  Is there a formula that will SUM the parents totals together?  Currently I've been having to click on the cell within each parent row and adding them together.  I'm always having to do this over and over, due to me moving / deleting rows etc.    

Maybe I'm thinking too hard about this and making it more complicated then it should be.  Any suggestions would be appreciated. 

 

Thanks for your time!

Daniel Stein

Hi Jen,

A good way to do this is to add a new row at the top of your sheet, and indent all the rows beneath it. This effectively makes all your current parent rows the children of a new top-level parent row. Then you can enter =SUM(CHILDREN()) in that top row to get what you are looking for.

You may find that multiple levels of hierarchy come in handy in other situations as well.

Hope this helps!

Daniel

Mike Wilday

Are you summing the parent rows in the same column as the children? 

Do you have a column that you could add some sort of indicator to declare a parent row? 

If you sum the parent row in a different column (maybe create a parent sum category) then you could just sum the parent row as in my screenshot.

1. Create a parentsum column. 
2. In the first parent cell type =SUM(CHILDREN(Cost1)) -- to sum the cost of the children of the cost column. 
3. Drag that formula down and you will see that only the parent row gets a sum... 
4. In your rollup area you can then choose =sum(parentrow:parentrow)

Put your rollup formula in a different column or you'll get a circular reference.

See screenshot. 

jenwillcockson

Thank you both for taking the time to help me out!  : )

 

Mike Wilday

Absolutely. Glad I could be of assistance.