How do I count the status of tasks and automate the sheet

FatAtty

Hello,

I am creating a dashboard which needs to show the status of all the projects (children rows) that fall under the subheading (parent rows) of "resourcing", "data strategy" etc...

Currently, my master sheet has a column for:

1. Task Name
2. %Complete
3. Status

What I want to do is count how many projects of each status (in progress, complete, not started etc.) there are so I can make a pie chart (or similar). In my data sheet (not master sheet), I have used the following formula:

=COUNTIF({Name of sheet}, "Not Started")

This formula has worked great, but it wont update if I add new rows or delete rows, and because I have multiple parent tabs, I don't want to select the whole column. I only want it to calculate the status of projects under each parent row.

Any ideas? Thank you in advance

KPH

Hi @FatAtty

I think you do need to select the entire column in your cross sheet reference to ensure the count updates when rows are added or deleted. But you can add a hidden column to identify the parent and child rows and then change your formula to only count the parent rows.

Step 1 - Identify parent and child rows

Add a column and this formula:

=IF(COUNT(CHILDREN([Task name]@row)) > 0, "p", "c")

This will put a "p" in the row if the row has children and a "c" if it does not. You can change this to 1s and 0s or whatever you like. If your data looks like I think it does, your table will appear like this:

You can make the formula a column formula and hide the column.

Step 2 - Adjust COUNTIF to count only child rows

Swap your COUNTIF for a COUNTIFS, adding a second range and second criteria to include only rows that are children.

=COUNTIFS({Name of range containing status column}, "Not Started", {Name of range containing the new column},"c")

For my example, this table is created: