SUMIFS to COLLECT DESCENDANTS
I am trying to rewrite some formulas in a large sheet due to the sheet crashing regularly. It is my opinion that the sheer mass of SUMIFS within this sheet is causing it.
Here is my current formula:
=SUMIFS($Actual:$Actual, $[Job Number]:$[Job Number], =$[Job Number]@row, $Department:$Department, ="Design", $Awarded:$Awarded, =1)
This formula is on the parent row, so I think I can make it work with DESCENDANTS and maybe a COLLECT formula, but I can't seem to figure it out. I'm not even sure that COLLECT is necessary since I'm using the SUMIFS.
The children rows are for different contractors, departments, and stages of the jobs to be tracked. Sometimes they overlap. (one contractor might work for two departments on the same job, or work on two different stages of the job.)
I've attached my template job so you can see the data fields I'm working with. The formula is used in another column of the sheet (not shown). More rows are added when the job reaches the next stage and the Classification column captures this data.
Can you please help me rewrite the SUMIFS formula to use DESCENDANTS so it won't have to look at the entire length of the sheet in three columns?
Comments

Thank you so much! I was stuck on that for two days before I finally made a post to ask for help. It worked perfectly!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 46 Industry Talk
 387 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!