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

Krunal
Krunal ✭✭✭✭
edited 09/23/22 in Formulas and Functions

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

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin
    Answer ✓

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!