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

Options
✭✭✭✭

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!

Tags:

## Best Answer

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭✭✭
Options

Where exactly are you trying to put the formula?

• ✭✭✭
Options

Instead of <100%, use <1.

• ✭✭✭✭
Options

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

Thanks!!

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 03/22/22
Options

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
edited 03/22/22
Options

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!

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Answer ✓
Options

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)

• ✭✭✭✭
Options

@Paul Newcome that worked!! Thanks!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!