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!

Tags:

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    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

  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/06/24

    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
    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
    KPH ✭✭✭✭✭✭
    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.


  • Saj
    Saj ✭✭

    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!