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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!