Countif Child rows with specific condition.

Options
✭✭✭

Hi All,

Need help on the formula am using in sheet summary.

Am trying to count child rows that has specific colour.

Getting error #UNPARSEABLE on below formula.

=COUNTIF(CHILDREN([Schedule Health]:[Schedule Health], "Red"))

Appreciate the help!

Tags:

Best Answer

• ✭✭✭✭✭✭
Answer ✓
Options

Ah, I thought as you spelt colour the British way that you must be British (where that symbol is a called a bracket).

I was only looking at your formula to correct the UNPARSEABLE error.

To count the number of red dots for child rows only you need to use a COUNTIFS function as you have two IFS to check. - is Schedule Health red and also is the row a child row.

To find out of the row is a child row, you can add a helper column (enter the formula, convert it to a column formula, and then hide it). In the example, I called my column "Count of Children". The formula for that is:

=COUNT(CHILDREN([Task Name]@row))

This will count how many children the row has.

Then in your COUNTIFS you can include only rows where the count of children is 0.

=COUNTIFS([Schedule Health]:[Schedule Health], "Red", [Count of Children]:[Count of Children], 0)

In my example, the answer would be 4.

Answers

• ✭✭✭✭✭✭
edited 02/06/24
Options

Try moving one of the closing brackets* at the end to close the CHILDREN function, leaving the other to close the COUNTIF:

=COUNTIF(CHILDREN([Schedule Health]:[Schedule Health]), "Red")

* From your spelling of colour I think I can use bracket rather than parenthesis. 😉

• ✭✭✭
Options

Thanks for the help KPH,

That's sill counting Monitoring + Task 7.

I only want to count tasks that are "Red". See attached picture.

PS: That's parenthesis 😉

• ✭✭✭✭✭✭
Answer ✓
Options

Ah, I thought as you spelt colour the British way that you must be British (where that symbol is a called a bracket).

I was only looking at your formula to correct the UNPARSEABLE error.

To count the number of red dots for child rows only you need to use a COUNTIFS function as you have two IFS to check. - is Schedule Health red and also is the row a child row.

To find out of the row is a child row, you can add a helper column (enter the formula, convert it to a column formula, and then hide it). In the example, I called my column "Count of Children". The formula for that is:

=COUNT(CHILDREN([Task Name]@row))

This will count how many children the row has.

Then in your COUNTIFS you can include only rows where the count of children is 0.

=COUNTIFS([Schedule Health]:[Schedule Health], "Red", [Count of Children]:[Count of Children], 0)

In my example, the answer would be 4.

• ✭✭✭
Options

Adding a helper column and COUNTIFS formula worked.

Many thanks😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!