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

Reema Hussain
edited 12/09/19 in Archived 2016 Posts

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! 



This discussion has been closed.