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

Options

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

Tags:

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!