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 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!
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!