Array formulas for project completion in Smartsheet


I have a project plan in Smartsheet with 2 columns:

  • % Complete
  • Relative weight of completion

This cells are used in parent rows (the phases of the project) and are used to indicate the completion of a phase but also the relative amount of effort that each phase has (as an average is not accurate enough)

I would like to have a cell in a parent to this rows to show the project completion. This is, multiply each row's "% complete" by the row's "Relative weight of completion" to get a total.

In Excel, I would use array formulas ({formula}) for this but, as far as I'm aware, this is not possible in Smartsheet, and I don't want to do it manually as the formula would need to be updated every time a row is added or removed.


So the goal would be that the "[Project]" goal would show:

=[% Complete]2*[Weight (% of Completion)]2+[% Complete]16*[Weight (% of Completion)]16+[% Complete]26*[Weight (% of Completion)]26+[% Complete]54*[Weight (% of Completion)]54+[% Complete]96*[Weight (% of Completion)]96+[% Complete]102*[Weight (% of Completion)]102

But also that, if I add any more children to the "[Project]" row, they would be automatically added to the calculation and I don't have to edit the formula manually (was thinking something that uses "CHILDREN()" but can't find any way).

Is it possible?

Thank you in advance!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!