SUMIF and Child rows

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

Answers

  • JayF
    JayF ✭✭

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

    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

  • JayF
    JayF ✭✭

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

    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

  • JayF
    JayF ✭✭

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!