Count if Children Rows are Completed

swirt009

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

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: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

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