Total Price Column for Parent is summation of Total price of Children

Krunal

Hi,

Can I know what formula to use in order to achieve Total Price Column for Parent is summation of Total price of Children. When I did convert to column formula it came with 0 value. I had used =SUM(CHILDREN()) and also the column properties is Text/Numbers.

Thanks in advance.

Krunal

Genevieve P.

Hi @Krunal

You will want to create a new column next to your current column, and put the formula in the new column, like so:

Or you can put =SUM(CHILDREN()) directly into the grey cell of the Total Price column, but then you will need to manually do this every time a new Parent is created. Does that make sense?

Genevieve P.

Hi @Krunal

=SUM(CHILDREN()) is the correct formula to use!

If you receive 0 as the output, can you try using SUM and selecting the range to see if you get the same result? In my example below I've added in the row numbers from row 5 - 15:

=SUM([Total Price (SF)]5:[Total Price (SF)]15)

If this still is giving you 0, how are the numbers below being generated? Are they manually put into the cell or do they come through a connector or a formula?

Cheers,

Genevieve

Krunal

Hi Genevieve,

I can get the total by =SUM([Total Price (SF)]5:[Total Price (SF)]15), but I cannot convert that into a column formula. The data is coming from a SF connector.

Do I need to work on the Parent example entry at the top of the sheet and then will it apply the formula to the new parent data which is synced via the connector?

Thanks

Krunal

Genevieve P.

Hi @Krunal

Thank you for clarifying! You are correct, this cannot be turned into a Column formula. The SUM(CHILDREN()) formula would be inserted into the highlighted cell above which could not be turned into a Column formula either since it's just for one cell.

What you'll want to do is set up a Helper Column where all it contains is the formula. Then in your formula you want to reference the appropriate column in the CHILDREN function, like so:

=SUM(CHILDREN([Total Price (SF)]@row))

Since you're referencing @row instead of a row number like 5 or 15, you can now set it as a Column formula!

Let me know if this works for you.

Cheers,

Genevieve

Krunal

Hi Genevieve,

=SUM(CHILDREN([Total Price (SF)]@row)) did work, but when I convert it into a column formula the whole column goes to 0.

Thanks

Krunal

Genevieve P.

Hi @Krunal

Yes, this is because none of the Child Rows have their own Children. This means that for their row, they are 0, but you should see the correct SUM in the one Parent row at the top. Is this correct?

You can make all the Child Rows blank if you'd prefer, like so:

=IF(COUNT(CHILDREN([Total Price (SF)]@row)) = 0, "", SUM(CHILDREN([Total Price (SF)]@row)))

Krunal

Hi Genevieve,

Please see below when I use =SUM(CHILDREN([Total Price (SF)]@row))

Am I doing something wrong here?

Thanks

Krunal

Genevieve P.

Hi @Krunal

You will want to create a new column next to your current column, and put the formula in the new column, like so:

Or you can put =SUM(CHILDREN()) directly into the grey cell of the Total Price column, but then you will need to manually do this every time a new Parent is created. Does that make sense?