Count if Children Rows are Completed

Hi All,

I need a formula that counts how many children tasks don't have a completed date entered yet. See the screenshot below.

My formula is:

=COUNTIFS(CHILDREN(Task@row), <>"", [Completed Date]:[Completed Date], "")

However, it returns an incorrect argument.

Any suggestions would be greatly appreciated!


  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 10/06/23

    Hello @swirt009 !

    You're close - the problem with your formula is that you are referencing the Children in the Task column when you should be referencing Completed Date since that contains the data you want to evaluate.

    This is the formula for you:

    =COUNTIF(CHILDREN([Completed Date]@row), NOT(ISDATE(@cell)))

    You could also put this directly into the Completed Date column by using "" at the end of the formula to force the Date column to display a text value.

    The formula is below and a screenshot:

    =COUNTIF(CHILDREN(), NOT(ISDATE(@cell))) + ""

    Personally, I like to show how many are completed out of how many there are total which looks like this:

    The formula I am using for this in the Completed Date column is:

    =COUNTIF(CHILDREN(), ISDATE(@cell)) + "/" + COUNT(CHILDREN(Task@row))

    Note that this formula requires a non blank value in Task for the denominator to be counted.

    Hope this helps!


    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries:

    ▶️ Smartsheet Tutorial Videos:

    PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!