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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!