Children sum formula total calculations

Hello everyone,

I have a formula issue that has stumped me. My use case is basically summing up all of the Job income per quarter. Our jobs often have differnt phases which are simply indented to the job itself. My primary Job rows are set to =SUM(CHILDREN) while the total wrap is just set to =SUM of all rows. My question/issue is that I need my main total wrap calculation to only factor in the Job parent row sums and not the children numbers as well. If you look at the attached screenshot, you will see its counting everything when I only want it to count the parents, in this example, it should only read $6. I know I can do this cell specific but we may create dozens of these Jobs parent rows with subsequent children rows underneath it so I dont think individually picking each cell for this calculation is efficient. Anyone know of a way for me to sum up ONLY the parent rows?


Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Two ways. One is to add a helper column that contains

    =COUNT(CHILDREN(Cost@row)) 
    

    Followed by your total cell containing

    =SUMIF([Helper Column]:[Helper Column], >0, Cost:Cost)
    

    The other is to simply add the total Quarter 1 wrap at the top of the items and indent all the information in one and use the same formula you are already using. Personally I prefer this way.

    =SUM(CHILDREN())
    


Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Two ways. One is to add a helper column that contains

    =COUNT(CHILDREN(Cost@row)) 
    

    Followed by your total cell containing

    =SUMIF([Helper Column]:[Helper Column], >0, Cost:Cost)
    

    The other is to simply add the total Quarter 1 wrap at the top of the items and indent all the information in one and use the same formula you are already using. Personally I prefer this way.

    =SUM(CHILDREN())
    


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!