Sum Child Rows when "children" are a different column

Options

I am sure this has been asked before, but I can't seem to figure out a solution that will work in my sheet. I am trying to sum the child rows of a specific column, but my formula isn't working. Can someone take a look at the screenshot and see if you can help?

I need to add the individual expenses (in the Expense Total column) for each item under "Lastname, Firstname" (Description column). Currently my formula in the Expense Total parent row is =sum(children[Expense Total]@row), but this isn't working, I'm guessing because the children are technically in the 'description' column. Is there a way I can write the formula to sum the individual expenses for the children of "Lastname, Firstname" in the Expense Total column?

Thank you in advance!


Tags:

Answers

  • J Tech
    J Tech ✭✭✭✭✭
    Options

    Hi,

    This should work:

    =SUMIF([Description]@row:[Description]@row, "Lastname, Firstname", [Expense Total]@row:[Expense Total]@row)

    This formula will sum the values in the Expense Total column that correspond to the rows where the Description column matches "Lastname, Firstname".

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • ejrbean
    ejrbean ✭✭
    Options

    Gave that a shot, but I got the error #circular reference. Any other ideas?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • Genevieve P.
    Options

    Hiya! Just hopping in here as I noticed you have your hierarchy set up in multiple levels.

    SUM(CHILDREN will work for one level down, but it looks like your totals are actually on the second Child level. If you will not have a summary on each of the direct parents, then you could use:

    =SUM(DESCENDANTS([Expense Total]@row))

    instead! 🙂

    Otherwise, Paul's suggestion will work as long as you put it in every level (e.g. in Conference row level as well as the top level)

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!