Formulas Done vs Complete

Ruvalcaba12 · 08/08/23

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 · 08/08/23

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 · 08/08/23

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 · 08/08/23

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 · 08/08/23

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.

Formulas Done vs Complete

Answers

Help Article Resources

Categories