SUMIF and Child rows

System

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

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.

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

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.

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

@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.

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