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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!