SUMIF and Child rows
Answers
-
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?
-
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
-
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
-
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
-
@Genevieve P. OMG! it worked. You are a lifesaver. I can't believe that's the only change I needed thank you so much!!!!
-
No problem at all! 🙂 You had the formula structure correct. Easy adjustment!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!