Summary Sheet Formula - counting live projects

Michelle Maas

We use a small project control sheet to manage all the small works that come into the business. I want to create a formula in the Summary Sheet to counts only live projects.

The top row header "Archive" is a PARENT for completed projects - I do not want to count these.

For any projects that have not been archived, the Finish column cell in the PARENT would indicate any live projects, i.e. < TODAY. I would like the formula to count these and any new additions to the sheet.

Another column that may be useful in this process is the Project Number.

Paul Newcome

Yes. You can leverage that.

=COUNTIFS(Level:Level, 1, [Task Name]:[Task Name], @cell <> "Archive")

Paul Newcome

Are you able to provide a screenshot that shows the layout?

Michelle Maas

Paul Newcome

Do you only want to count the parent rows, or do you want to include the child rows in your count as well?

Michelle Maas

This formula is just for counting how many projects are "live" (and not archived, which would mean they are complete).

Paul Newcome

Right, but I see that the grey rows also have child rows underneath of them. Do you want to count those children or just the grey rows? If just the grey rows, do their children have the [Project Number] column populated? What about the child rows under the Archive parent? Do they have the [Project Number] column populated?

Michelle Maas

Yes the child rows of both active and archive have the project number column populated. The parent shows which project it is, the children shows the states of the project, which vary in formatting. Most have Enquiry through to Close Out, but others have just the task rows for very small jobs. Therefore, I think it is practical just to count the grey rows as this contains the project name, number and calculates the start to end dates from the children it relates to.

Paul Newcome

In That case we are going to want to insert a column that will automatically determine which rows to count and then count this new column. Using a text/number column (that can later be hidden to keep the sheet looking clean) it would look something like this...

=IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row <> "Archive"), 1)

Then you can either use a COUNT or SUM function on this new column.

Michelle Maas

Ah I already have a column [Level] for formatting the parent/children rows (formula below). Could I use that?

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

Paul Newcome

Yes. You can leverage that.

=COUNTIFS(Level:Level, 1, [Task Name]:[Task Name], @cell <> "Archive")

Michelle Maas

You are awesome! Thank you so much, formula now capturing accurate count.

Paul Newcome

Happy to help. 👍️