Using DESCENDANTCELLS for Subtotals in a Project Budget

A new Project Budget template in the gallery is available that demonstrates the use of the formula SUM(DESCENDANTCELLS()) function to calculate subtotals for hierarchical budget categories. This uses the Indent/Outdent feature in Spreadsheet.com.

The Budget column in this worksheet calculates the cost for each task by adding the fixed costs, materials, labor and other costs. To avoid showing a subtotal in this column, it uses the CHILDCELLS function to check whether the row has any children in the hierarchy, displaying a dash for the summary task rows: IF(COUNTA(CHILDCELLS(A))>0,"-",E+FG+HI+J)

Links to Function Documentation:
DESCENDANTCELLS
CHILDCELLS

If you have comments/questions about this template, feel free to reply to this topic.

5 Likes