What does DESCENDANTS return? Can it be used for a range?

I am trying to make a task counter that counts task that are children of a particular cell. However, if they are children of children, it should only count the grandchildren.

=COUNT(DESCENDANTS($[Task List]$53)) works but counts child rows with children which I don't want to count.

I added a helper column that returns a 1 or a zero depending on whether I want to count the task or not. This basically says if the task does not have children, return 1, if it does have children, return 0 so it doesn't get counted.

=IF(COUNT(CHILDREN([Task List]@row)) = 0, 1, 0)

Then to get the total I would like:

=COUNTIF(DESCENDANTS($[Task List]$53), [H Project Phase]@row = 1)


But this returns 0

I don't think I understand what DESCENDATS returns without a count function in front of it.

I don't want to give a row reference because in testing the row reference did not track adding and removing rows, which will happen.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 01/26/24

    Hey @EMACTX

    DESCENDANTS counts every single child row beneath a parent, regardless of level of indent.

    If you only want to subtract the first level of child, I would use the CHILDREN function which will only see the first level.


    Try something like:

    =COUNT(DESCENDANTS($[Task List]$53)) - COUNT(CHILDREN($[Task List]$53))

    However if there are times where you want to count the Children because there are no sub-tasks, then you can use an IF statement to figure this out:

    =IF(COUNT(DESCENDANTS($[Task List]$53)) = COUNT(CHILDREN($[Task List]$53)), COUNT(DESCENDANTS($[Task List]$53), (COUNT(DESCENDANTS($[Task List]$53)) - COUNT(CHILDREN($[Task List]$53)))

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!