How to count children cells from % complete column  countif not working
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!
Best Answer

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)
Answers

Where exactly are you trying to put the formula?

Instead of <100%, use <1.


@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!!

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@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!!

Happy to help!
It should work as long as you add the formula in another column or the Sheet Summary section.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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!

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!!!!!!

@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!

Excellent!
You'd need to use a column in the formula to let what cells are children or add a socalled 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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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 that worked!! Thanks!!!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!