Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Count children rows only if items are not marked complete
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?