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
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!