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