How to Count Children Status Only

I am building a metric sheet that will pull "Completed" status from multiple project sheets. I am trying to write a formula that will exclude Parents from the count. Below are the status field values.

In-progress
Complete
Not started
On-hold

Formula below is counting the status for complete but I want to modify it so that it does not count the parents which also may say complete.

=COUNTIFS({B1035 Shared PC Deployment}, "Complete")

How can I change the above formula to exclude the parents. We have over 3 dozen projects each with their own project sheet that my metric sheet is currently pulling data from for dashboard purposes. Right now the parents are still being included in the counts.

Any help would be greatly appreciated.

Find more posts tagged with

Accepted answers

Rich Coles

We got there in the end! The ancestors column is a great help for many things, which is why I always add it - although I usually name it Level.

Please do mark the answer as accepted so it flags as such for others!

All comments

Rich Coles

Hi @RJT - wise to exclude the parents as otherwise this skews the actual tasks count! I pretty much always add into my sheets with hierarchy a Level column with the following formula: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS())).

Using this, I then can add a further condition to the COUNTIF formula: {Level Range in other sheet}, =""

Hope that helps

Rich

RJT

Rich,
I am not sure I understand how to apply the solution you provided. I created a column and applied the IF formula to the the column. The question would not be how to I draft the COUNTIF formula in the metric sheet to pull the status that display COMPLETE from the project page.

Rich Coles

Hi @RJT - apologies if not clear! The challenge you have is to identify the cells that are children - I do this by default on all my sheets with hierarchy for this eventuality by adding in a Level column using the formula I shared above. The solution I'm suggesting works on the basis of having this column to reference so you would need to add it in to your sheets, unless someone has a funky formula that can count without the need for this.

I've not needed to crack that formula given I have my Level column in place as I use it for a variety of functionality (eg conditional formatting). If you have control center, you could add in the column to existing sheets using the global update, otherwise it will need to be done manually.

It then comes down to how you are compiling the metrics across multiple projects, for example using summary sheet metrics in a report or referencing the individual sheets. If you are referencing multiple sheets, then I would want a portfolio sheet in place that includes a column to bring back the calculation from each project in each row that can then be used for the total. Difficult for me to guide any further without seeing your set-up so worth sharing some screen-shots unless someone else can crack based off your initial description. Or drop me a line via our website using the link in my signature

Example of the formula in a sheet summary that excludes parents from the count of complete items.

RJT

@Rich Coles I greatly appreciate your insight and knowledge. Below is some detail of what I am attempting. Again your assistance is much appreciated. The status information is Drop Down text with values of Complete, Not Started, In-Progress and On-Hold. The bottom left countif is counting the string "Complete" but it is counting them all. Trying to remove the parent counts.

Rich Coles

Aha! Simpler than I thought… in you metric sheet, add on to your formula and make it a COUNTIFS again:

=COUNTIFS({B1035 Shared PC Deployment}, "Complete", {B1035 Shared PC Ancestors},"")

Note the {B1035 Shared PC Ancestors} is a range from the Ancestors column in your other sheet. What this formula is doing is only counting items that are (1) complete and (2) do not have a value in the Ancestors column

Rich

RJT

@Rich Coles You are the man good sir. Worked Perfectly. I greatly appreciate the help.

Rich Coles

We got there in the end! The ancestors column is a great help for many things, which is why I always add it - although I usually name it Level.

Please do mark the answer as accepted so it flags as such for others!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.