Formula for Summary of Total Tasks and Completed Tasks

dblasco

There is a smartsheet template set in the solution center called New Employee Onboarding. 

In the sheet "30 Day Employee Onboarding Sheet" the tasks are organized by Week and By Day. The formulas for counting Completed: # of # are working for each day, but the Cells that have a Weekly Total are not summarizing for the week, they say Completed 0 of 0. Is there a way to update this formula so that it will summarize all of the tasks for the week and show how many are completed: total of Total for that week?  I can do if if i just add my exact range, but would like it to reference all of the children and grandchildren tasks automatically for that week, since each week will have a different number of tasks as the plan is built out.

 

="Completed: " + COUNTIF(CHILDREN(Done11), 1) + " of " + ((COUNTIF(CHILDREN(Done11), 1) + (COUNTIF(CHILDREN(Done11), 0))))

 

Not sure if you can see the template I saved down in my Workspace, here's the link:https://app.smartsheet.com/sheets/VgcRFW9QqWC4Mw2WgXR2pwjPQ8jmhgcxRFP8qGg1

 

You can also find it in the solution center: Template Set- New Employee Onboarding

Nic Larsen

It looks like they didn't write a complete formula.. at least to me. 

Try this in the Week 1 cell - although there is probably a more elegant formula to write:

="Completed: " + SUM(COUNTIF(CHILDREN(Done24), 1) + COUNTIF(CHILDREN(Done33), 1) + COUNTIF(CHILDREN(Done35), 1) + COUNTIF(CHILDREN(Done37), 1) + COUNTIF(CHILDREN(Done39), 1)) + " of " + SUM(COUNTIF(CHILDREN(Done24), 1) + (COUNTIF(CHILDREN(Done33), 1) + (COUNTIF(CHILDREN(Done35), 1) + (COUNTIF(CHILDREN(Done37), 1) + (COUNTIF(CHILDREN(Done39), 1))))))

 

 

 

Paul Newcome

Based on your screenshot and the formula in your post, it looks like it should be working as a summary row. However, because there are specific row references, if you happened to add/delete/change the hierarchy of any rows, it could throw things off. Try replacing the row number with @row.

 

.........CHILDREN(Done@row)..............

 

This will automatically reference whatever row the formula is in which will ensure you are in fact referencing the correct row. I find this very helpful in sheets that have a lot of changes going on.