Countif Child rows with specific condition.
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!
Best Answer

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

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

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 😉

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.

Adding a helper column and COUNTIFS formula worked.
Many thanks😀
Help Article Resources
Categories
Check out the Formula Handbook template!