How to count children cells from % complete column - countif not working

Tana V

This is for my % complete column. I'm trying to count the children tasks if the % Complete is less than 100%.

I'm using this formula: =COUNTIF(CHILDREN([% Complete]):([% Complete]), <100%) but it is not returning a count, just displays the formula as it isn't correct syntax but can't figure it out.

Thanks in advance!

Paul Newcome

I would suggest a helper column with this column formula:

=COUNT(CHILDREN(PercentComplete@row))

Then your COUNTIF would change to a COUNTIFS and look something like this:

=COUNTIFS(PercentComplete:PercentComplete, <1, [Helper Column]:[Helper Column], @cell = 0)

Paul Newcome

Where exactly are you trying to put the formula?

Yanis Kyr

Instead of <100%, use <1.

Tana V

@Paul Newcome the formula is in an empty cell at the bottom of the % Complete column.

Thanks!!

Tana V

@Yanis Kyr unfortunately that didn't work. It's like it won't accept it as a formula, just like it's text. Haven't been able to see a result yet (even a wrong one), just the formula. Thanks!!

Andrée Starå

Hi @Tana V

I hope you're well and safe!

If you're using the Dependencies feature, you can't use formulas in the % Complete column.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Tana V

@Andrée Starå thanks!!

Wow! I'm sure that is the problem. I'm trying to get a count of incomplete tasks only so I can reference the count on a dashboard. I have the count on a report but I can't reference a report on a grid widget on the dashboard. Any other ideas on how to get this count onto my dashboard?

Thanks again!!

Andrée Starå

@Tana V

Happy to help!

It should work as long as you add the formula in another column or the Sheet Summary section.

Tana V

@Andrée Starå

So close!!! I changed the cell I am putting the formula in and now getting a return of #UNPARSEABLE.

=COUNTIF(CHILDREN[% Complete]:[% Complete], <1)

Any thoughts, seems like progress!

thanks again!

Tana V

So I changed a couple things (column name mainly) and finally got it to recognize that it is a formula!! Yeah!

=COUNTIF(CHILDREN(PercentComplete:PercentComplete), <1)

But it is still counting both children and parent / summary rows??

Any thoughts? Thanks!!!!!!

Tana V

@Paul Newcome just wanted to see if you might be able to help -- my formula is now counting but is including both parent and children rows.

=COUNTIF(CHILDREN(PercentComplete:PercentComplete), <1)

Is there something I am doing wrong with the CHILDREN function?

Thanks!

Andrée Starå

@Tana V

Excellent!

You'd need to use a column in the formula to let what cells are children or add a so-called helper column if you don't have one to use.

Try something like this.

=COUNT(ANCESTORS())

Make sense?

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I would suggest a helper column with this column formula:

=COUNT(CHILDREN(PercentComplete@row))

Then your COUNTIF would change to a COUNTIFS and look something like this:

=COUNTIFS(PercentComplete:PercentComplete, <1, [Helper Column]:[Helper Column], @cell = 0)

Tana V

@Paul Newcome that worked!! Thanks!!!

Paul Newcome

Happy to help. 👍️