Need help creating formula! =SUM(CHILDREN([Time off Personal]@row<'x'))

tjernejcic

Trying to create a formula that counts number of days off in children under parent row. Any advice???

Amber Eakin

Hi @tjernejcic - Absolutely! Give this a try. You can put this into the Total column.

=SUM(CHILDREN([Time Off Personal]@row:[Time Off CFE]@row))

If you're only looking for one at a time, it would be =SUM(CHILDREN([Time Off Personal]@row))

tjernejcic

@Amber- Thank you for the quick response. I am still getting unparseble with this new formula?!

Amber Eakin

Ah, I notice that those columns have a column formula. I often have issues with formulas that reference a cell that has a column formula. Hopefully someone else can chime in!

tjernejcic

Oh thank you for trying!!! I appreciate all the help I can get!

Paul Newcome

The issue is not that the data is being populated via column formula but because we can't use multi-column ranges like that in the hierarchy based functions such as CHILDREN.

You can either put

=SUM(CHILDREN())

in each of the parent rows of those columns, or you can sum each column individually and add them all together (a couple of different ways).

=SUM(CHILDREN([Column 1]@row)) + SUM(CHILDREN([Column 2]@row)) + SUM(CHILDREN([Column 3]@row))

or

=SUM(SUM(CHILDREN([Column 1]@row)), SUM(CHILDREN([Column 2]@row)), SUM(CHILDREN([Column 3]@row)))