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

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:

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")
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.

Find more posts tagged with

Accepted answers

All comments

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"))

=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)

Screen Shot 2021-02-25 at 1.44.07 PM.png

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.