Metrics - how to question

I want to change up how I am calculating metrics - currently counting tasks by team / completed tasks but this looks like not much progress is being made.

Instead we would like to do this more by points or % of larger categories.

For example: EVAC tasks would total 50% of the overall project, AWARE/community tasks, 40% and planning/kickoff 10%.

My question is how do I achieve this? I tried to have the completion points column be a % column but struggled.

Let's say within EVAC:

user roles = 5%

agency approved zone process = 25%

preplan book = 20%

training/drills = 5%

how do I setup the sheet that each task in those sections add up to the % noted above and then on my metrics sheet I see actual numbers beside 0.

Hope that makes sense.


  • Michael Pappas
    Michael Pappas ✭✭✭✭

    If your metrics are being tracked in Sheet Summary or on another sheet, I think the easiest way to do it would be to manually enter the values in the Completion Points column, then use a SUMIF or SUMIFS formula to calculate the metric. You can use a formula in your parent rows to have checkbox check when all child rows have been checked.

    The SUMIF/SUMIFS formula could use Responsible Party, Assigned To, and/or Completed? to determine which values from the Completion Points column to SUM depending on how your Responsible Party and Assigned To columns are used.

    If you want to do it directly on this sheet, I would probably manually enter the Completion Points value for all of tasks that do not have Child rows, then put =SUMIF(CHILDREN([Completed?]@row,1,CHILDREN()) in the completion points column for all rows with children. Then, your MAT Team 1 row will have a value of 90 when all subtasks are complete, EVAC Tasks would have a value of 50, etc.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!