Count children rows only if items are not marked complete

Reema Hussain

I have a parent with 22 children. I can use a simple formula to calculate the total number of children ="Site-Wide" + " (" + COUNT(CHILDREN()) + ")"

 

What if I only want to count the children that haven't been marked as completed i.e. tasks that are still open? I'm trying to use this formula but I'm getting an #Invalid Operation error.

=IF(Complete1:Complete23 = 0, "Site-Wide" + " (" + COUNT(CHILDREN()) + ")")

 

What I'm trying to say is to check cells 1-23 in the Complete column and count the children the children in that column if the cell is empty i.e. the task hasn't been checked as complete. I don't think an IF statment will work here because it'll only count the children if all the cells from 1-23 are empty which won't be the case. I think I need a FOR loop to go through each cell one at a time, check its status and then count it accordingly. Or maybe a CountIf?? Can someone please point me in the right direction?

 

Thank you! 

Reema Hussain

I figured it out! (Not sure how to delete the question....)