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

Ryan Finney
Ryan Finney ✭✭✭
edited 06/21/22 in Formulas and Functions

(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.

Answers

  • Ryan Finney
    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.
    Genevieve P. Employee Admin
    edited 06/23/22

    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
    Ryan Finney ✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!