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

Charlene Pons

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?

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

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

Mark