Formulas Done vs Complete

Ruvalcaba12

With the formulas I currently have they work just fine. The small detail is that when they are not completed it says "2 of 3 Done" but when completed it says "6 of 6 Completed" (See screenshot). I know its minor but I was hoping to for a way to keep it consistent either all Done or all Complete.

These are the formulas I used. I tried changing them around but when I do it either says #UNPARSEABLE or the black line "1 of 4 Done" stops accurately keeping track. Does anyone know an easy way to get this fixed?

=COUNTIFS(CHILDREN(), CONTAINS("Complete", @cell)) + " of " + COUNT(CHILDREN()) + " Done"

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), COUNTIFS(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Complete", COUNTIFS(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done")

Thanks!

Paul Newcome

In each place of your formula where there is either "Done" or "Complete", switch whichever one you don't want to whichever one you do want.

Ruvalcaba12

https://community.smartsheet.com/discussion/comment/389609#Comment_389609

I tried that already. When I do that it works for the formula in purple but for the formula in black it just goes to "4 of 4 Done". It messes with the overall tracker.

Cory Page

Hello, maybe something like this might be a start?

=IF(COUNT(CHILDREN([Helper column (within the past 7 days)]2:[Helper column (within the past 7 days)]8)) = COUNTIFS([Planning Done]3:[Planning Done]8, 1), "Complete", "Error")

Then if its not completed but you still want to see the count you could do this

=IF(COUNT(CHILDREN([Helper column (within the past 7 days)]2:[Helper column (within the past 7 days)]8)) = COUNTIFS([Planning Done]3:[Planning Done]8, 1), "Complete", COUNTIFS([Planning Done]3:[Planning Done]8, 1) + " " + "Completed")

They both compare the total number of children to the total number of completed checks, seems to be pretty close to what you're looking for if not feel free to reply..

Paul Newcome

Ok. I see how things are working now. Sorry about that. Insert a (hidden) checkbox helper column and use this column formula:

=IF(COUNT(ANCESTORS([Task/Topic]@row))=1, IF(COUNTIFS(CHILDREN([Planning Done]@row), @cell <> 1) = 0, 1)

Then adjust the formula in the very top row to count how many of these boxes are checked.