COUNTIF formula that will return the # of DESCENDANTS from a specific Task and exclude CHILDREN

Ryan Finney

(New User btw) Understand how to get the # of total Descendants from a specific Task, but want to exclude the # of Children so I am only counting Grandchildren.

Would also like to have the formula FIND a specific Task name and use that as the Task reference.

I have an "Indent Level" column and have tried using it so that the formula would only count Indent Level = 2 but could not get it to only count the tasks that = 2 for a specific parent task instead of all tasks.

Ryan Finney

I have it working so far with

=SUM(COUNT(DESCENDANTS(Tasks1)) - (COUNT(CHILDREN(Tasks1)))).

Now just need to get the formular to FIND a specific task to use in place of (Tasks1).

Genevieve P.

Hi @Ryan Finney

What I would do in this instance is use the @row function instead of a row reference after "Tasks", like so:

=SUM(COUNT(DESCENDANTS(Tasks@row)) - COUNT(CHILDREN(Tasks@row)))

See: Create Efficient Formulas with @cell and @row

Then you could apply this as a column formula:

If you don't want the other rows to show 0, you could add an IF statement that says If there are no Ancestors to this row, meaning it's the top level, do this formula. Otherwise return a blank cell.

=IF(COUNT(ANCESTORS(Tasks@row)) = 0, SUM(COUNT(DESCENDANTS(Tasks@row)) - COUNT(CHILDREN(Tasks@row))))

Would this work for you? If not, could you clarify why you want to formula to find a specific task name? (Is this formula ins a separate sheet, or in a different row than the grandparent?)

Cheers,

Genevieve

Ryan Finney

Sorry did not reply. This helped a lot Geneveive. Thanks!