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

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

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

  • Saj
    Saj ✭✭✭
    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 😉

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


  • Saj
    Saj ✭✭✭
    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!