Count only child rows that have "Complete" in 3 columns

Hi I am trying to count the number of child rows that have "Complete" in 3 different columns. Here is my current formula:

=COUNTIFS((CHILDREN)1, [HS Germ]:[HS Germ], "Complete", [HS Pave]:[HS Pave], "Complete", [Classroom]:[Classroom], "Complete")


I keep getting "UNPARSEABLE". Any suggestions?

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Charlene Pons

    Do the Child rows you want counted all below to a single parent? If yes, try

    =COUNTIFS(CHILDREN([HS Germ]1), "Complete", CHILDREN([HS Pave]1), "Complete", CHILDREN(Classroom1), "Complete")

    If you're trying to count any Complete child row in the columns regardless of their parent and regardless of hierarchy level, I would add a helper column to designate Parent rows.

    This helper column is often a checkbox column. I'll call it Parent

    =IF(COUNT(CHILDREN([your primary column name]@row))>0, 1)

    Then your COUNTIFS becomes

    =COUNTIFS([HS Germ]:[HS Germ], "Complete", [HS Pave]:[HS Pave], "Complete", Classroom:Classroom, "Complete", Parent:Parent, 0)

    cheers,

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    =COUNTIFS(CHILDREN([HS Germ]@row) "Complete", children([HS Pave]@row), "Complete", Children([Classroom]@row), "Complete")

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!