Countif Child rows with specific condition.

Saj

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!

KPH

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.

KPH

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. 😉

Saj

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 😉

KPH

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.

Saj

Adding a helper column and COUNTIFS formula worked.

Many thanks😀