SUMIFS to COLLECT DESCENDANTS

ChelseaH

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?

Paul Newcome

You would use the DESCENDANTS function in the same way as the CHILDREN function. You would use it to set the range.

 

=SUMIFS(DESCENDANTS($Actual@row), DESCENDANTS($[Job Number]@row), =$[Job Number]@row, DESCENDANTS($Department@row), ="Design", DESCENDANTS($Awarded@row), =1)

ChelseaH

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!