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

Denera McCullough

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.

Genevieve P.

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

Denera McCullough

@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

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