Converting Cell to Column Formula
We have this sheet:
I am trying to have a column formula that will calculate the Burdened Labor for each row where we have a Start Date, Finish Date, Assigned To and FTE. We want to skip any rows checked as Milestones, setting their value to 0. And for the first row sum all the rows below it.
I want a column formula so that the user doesn't have to do anything if they add rows.
I have this formula:
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF((Finish@row  Start@row) / 7 > 0, (((Finish@row  Start@row) / 7) * FTE@row) * ({Protein Theraputics Tasks and Effort Range 1} / 52), 0), IF(PARENT([Task Name]@row) = "", SUM(2), 0))
If I use cell formulas I can replace the SUM(2) (just a placeholder) with SUM rows 2  120 but I would have to keep changing manually if rows are added.
If I replace the SUM with a SUMIF([Assigned To]:[Assigned To],<> "", [Burdened Labor]:[Burdened Labor]). I get Blocked and Circular Reference.
Any ideas would be appreciated.
Thanks.
/marc
Answers

Try replacing it with SUM(CHILDREN()).

That returns 0. I assume it is due to the fact that it has no direct children
/marc

DESCENDANTS() worked.
Thanks.
/marc

That's odd. For it to have any descendants at all it must have direct children.
As long as it works. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!