SUMIF and Child rows

Options
This discussion was created from comments split from: Can I use a SUMIF formula to sum children rows?.

Answers

  • JayF
    JayF ✭✭
    Options

    @Andrée Starå

    Question here, I think I have to use all these: SUMIF, INDEX/MATCH, and CHILDREN, which is where I'm getting stuck.

    Ive tried using this =SUMIFS(CHILDREN(Payment Amount@row), CHILDREN(Job@row), <>"Ground Floor Walls") (but where it says "ground floor walls" I click on the cell).... the problem is it calculates everything in the children rows and not just ground floor walls.

    But when I try using this =SUMIF(Job:Job, Job@row, [Payment Amount]:[Payment Amount]) the range goes past the children rows.

    I need it to Index/match "ground floor walls" only under the children rows. I don't want to use a range because otherwise I'll have to keep changing the formula everytime I add a child row which isn't ideal.

    Any ideas?

  • Genevieve P.
    Options

    Hi @JayF

    I've tried your formula and it works for me:

    =SUMIFS(CHILDREN([Payment Amount]@row), CHILDREN(Job@row), <> "Ground Floor Walls")



    The result here ignores the 10 in the one child row that says "Ground Floor Walls"




    However it sounds like you only want cells that say "Ground Floor Walls", is that correct? In that case, you wouldn't need the <>

    =SUMIFS(CHILDREN([Payment Amount]@row), CHILDREN(Job@row), "Ground Floor Walls")

    If this hasn't helped, can you post a screen capture of your sheet, but block out sensitive data?

    Cheers,

    Genevieve

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

  • JayF
    JayF ✭✭
    Options

    Hey @Genevieve P. , thanks for reviewing this! Yes, you got it, I need the sum of only the cells that say "ground floor walls" I just entered random numbers in there to test. Under total payment is where I want the formula. The reason I want only the children, is if you notice under the invoices there is a blue line that would be building 2 where I'd be repeating this


  • Genevieve P.
    Options

    Hey @JayF

    Thank you, the screen capture helps! I can see that the immediate Children of "INVOICES" is actually the invoice number (slightly lighter green) rows. This means there are no Children that have "Ground Floor Walls".

    Instead, try the DESCENDANTS function so it looks at all levels underneath it:

    =SUMIFS(DESCENDANTS([Payment Amount]13), DESCENDANTS(Job13), Job@row)

    Cheers,

    Genevieve

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

  • JayF
    JayF ✭✭
    Options

    @Genevieve P. OMG! it worked. You are a lifesaver. I can't believe that's the only change I needed thank you so much!!!!

  • Genevieve P.
    Options

    No problem at all! 🙂 You had the formula structure correct. Easy adjustment!

    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!