Countif formula if a row doesn't have any children


Is there a way to write a COUNTIF formula to count the rows that do not have any children?  I would like to write a COUNTIFS formula that counts:

1. % Complete is < 1

2. Finish is in the past

3. Row doesn't have any children

I know how to write the first two.  I'm struggling to write the 3rd one about children.  Below is a screenshot of what I'm working with.

ancestor formula.JPG



  • Shaine Greenwood


    I think that you might need another column to define parents here so you can count parentless rows.

    If you create a new text/number column (you can hide it later) and place a formula like this in every cell: =IF(COUNT(CHILDREN(Type@row)) > 0, 0, 1)

    Where Type is the name of the column you're trying to count, you'll be able to include that in your COUNTIFS function:

    =COUNTIFS([% Complete]:[% Complete], < 1, Finish:Finish, < TODAY(), Parent:Parent, 1)

  • brandon.elmer

    That's a good idea Shaine and I might use that.  The only thing is that if project managers are building/making changes to their project plans, then I will have to manually drag the formula down to the new rows.  I was hoping to avoid that.

  • brandon.elmer

    Hi Shaine,

    Could the formula =IF(COUNT(CHILDREN([Task Name]5)) > 0, 0, 1) that you wrote be turned into a COUNTIF formula?  

    I'm trying =COUNTIF(CHILDREN([Task Name]:[Task Name]), = 0) but it returns a 0.

    Any thoughts?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!