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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!