% of tasks in a specific status (in progress, not started, complete) by process and by team

Options

Sample Plan Preview is below. Note: The % displayed in the column only counts for completed tasks at a parent level =IF(COUNT(CHILDREN([Tasks Name]@row)) > 0, COUNTIFS(CHILDREN(Status@row), "Complete") / COUNTIFS(CHILDREN(Status@row), <>"N/A"))


I Need Assistance:

  • By team: % of children tasks that are "Not Started", "In progress", etc. minus the total tasks items that are "N/A" within the team
  • By Process: % of children tasks that are "Not Started", "In progress", etc. minus the total asks items that are "N/A" within the team
  • I would like to use a Metric sheet since I'm created dashboards based on the results.


I created a Metrics sheet below and unfortunately when I marked all tasks complete for all processes under G-Team I was provided with 20% rather than 100%.

Formulas I've used:

  1. Formula I used to get the total "complete" by process divided by overall task minus "N/A" tasks to get a % of "Complete" by process =(COUNTIFS({Reference Doc Range 1}, "Complete", {Reference Doc Range 3}, "Process A", {Reference Doc Range 2}, "G-team") + COUNTIFS({Reference Doc Range 1}, "Complete", {Reference Doc Range 3}, "Process B", {Reference Doc Range 2}, "G-team")) / COUNTIFS({Reference Doc Range 1}, <>"N/A")
  2. Formula I used to get the total not started by team divided by overall task minus "N/A" tasks to get a % of "Not started" by team =COUNTIFS({Reference Doc Range 1}, "Not Started", {Reference Doc Range 2}, "G-team") / COUNTIFS({Reference Doc Range 1}, <>"N/A")

*** I've used others and it still didn't make sense mathematically.


Answers

  • Genevieve P.
    Options

    Hi @Denera McCullough

    Can I clarify, what are you looking to divide by? Currently your formulas are dividing the Count of G-Team Status rows by all the tasks in the entire sheet that aren't N/A (regardless of Team or Process), is that correct?

    For example, if I had the following 3 rows:

    G Team / Process A / Complete

    G Team / Process B / Complete

    J Team / Process A / Complete


    Your formula would divide 2 / 3, since the J Team has a row that is <> N/A.

    Are you wanting to filter the count of total tasks that aren't N/A by process and team as well?

    If so, you'll need to add in your criteria and ranges into the final COUNTIFS function. If you just want to check the G Team as a whole, then you can do this:


    =(COUNTIFS({Reference Doc Range 1}, "Complete", {Reference Doc Range 3}, "Process A", {Reference Doc Range 2}, "G-team") + COUNTIFS({Reference Doc Range 1}, "Complete", {Reference Doc Range 3}, "Process B", {Reference Doc Range 2}, "G-team")) / COUNTIFS({Reference Doc Range 2}, "G-team", {Reference Doc Range 1}, <>"N/A")


    If you have more processes listed than Process A and Process B so you need to filter by process as well, this will need to be included in your formula, too:

    =(COUNTIFS({Reference Doc Range 1}, "Complete", {Reference Doc Range 3}, "Process A", {Reference Doc Range 2}, "G-team") + COUNTIFS({Reference Doc Range 1}, "Complete", {Reference Doc Range 3}, "Process B", {Reference Doc Range 2}, "G-team")) / COUNTIFS({Reference Doc Range 2}, "G-team", {Reference Doc Range 1}, <>"N/A", {Reference Doc Range 3}, OR(@cell = "Process A", @cell = "Process B"))

    Does this make sense? Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Denera McCullough
    Options

    @Genevieve P

    Clarity: Filter the count of total tasks that aren't N/A by process and team

    Note: I have 4 teams but each team have their own process Example: E-Team: Process C & D, B-Team: Process E & F

    • By G-Team:
      • [(Process A "Completed" tasks - Process A "N/A" tasks ) + (Process B "Completed" tasks - Process B "N/A" tasks )] DIVIDE BY [(Process A ALL tasks - Process A "N/A" tasks ) + (Process B ALL tasks - Process B "N/A" tasks )]
    • By Process:
      • (Process A "Completed" tasks - Process A "N/A" tasks ) DIVIDE BY (Process A ALL tasks - Process A "N/A" tasks )
      • (Process B Completed tasks - Process B "N/A" tasks ) DIVIDE BY (Process B ALL tasks - Process B "N/A" tasks )
    • Above I want to swap out completed with In progress and not started. I need it for each Status

    Thanks for the formulas. I will check it out to see they work

  • Michele Rainforth
    Options

    Hi Denera,

    If I'm understanding your end goal correctly, I believe I was able to achieve the correct percentages by slightly changing your formula so that children that had children didn't get double counted:

    =IF(COUNT(CHILDREN([Tasks Name]@row)) > 0, COUNTIFS(CHILDREN(Status@row), "Complete") / COUNTIFS(CHILDREN(Status@row), <>"N/A"))

    to

    =IF(COUNT(CHILDREN([Tasks Name]@row)) > 1, COUNTIFS(CHILDREN(Status@row), "Complete") / COUNTIFS(CHILDREN(Status@row), <>"N/A"))

    To achieve the summary metrics, I used the following formula to get total percentages:

    =COUNTIFS($[Child count]$2:$[Child count]$21, >1, $Team2:Team21, Team@row, $Status$2:$Status$21, =Status@row) / COUNTIFS($[Child count]$2:$[Child count]$21, >1)

    I did this on the same sheet, so if I'm on the right track, we can transfer the formulas to a separate metrics sheet.

    Let me know if this helps, and good luck!

    Michele

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!