Converting Cell to Column Formula

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

Try replacing it with SUM(CHILDREN()).

• ✭✭✭✭
Options

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

/marc

• ✭✭✭✭
Options

DESCENDANTS() worked.

Thanks.

/marc

• ✭✭✭✭✭✭
Options

That's odd. For it to have any descendants at all it must have direct children.

As long as it works. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!