How do I count the status of tasks and automate the sheet
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:
- Task Name
- %Complete
- 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
Answers
-
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:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!