Hierarchy Formula

FrankOrtz

Hello!

I've been trying to count how many milestones called "Testing" with %Complete = 100% are per project. Each milestone is a child of a parent called "Milestone" and this is at the same time a child of the project name. At the end, each project is a child of a big project name. (I've attached an example of the structure). 

I have read others comments and documentation of the hierarchical formulas, however, it's not clear to me how to develop it correctly. 

If someone can help me to elaborate it, I'll appreciate it a lot. I'm learning in the process. 

 

Thank you!

 

 

 

 

 

Andrée Starå

Hi,

Try something like this.

I've used CHILDREN in my example, but you could change the ranges if you need.

=COUNTIFS(CHILDREN([% Complete]1); "1"; CHILDREN([Big Project Name]1); FIND("testing"; LOWER(@cell)) > 0)

The same version but with the below changes for your and others convenience.

=COUNTIFS(CHILDREN([% Complete]1), "1", CHILDREN([Big Project Name]1), FIND("testing", LOWER(@cell)) > 0)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Andree:

A few observations if I may...

.

I feel like using the CHILDREN function may actually keep this from working as it would only be looking at the child rows of the specified cell. You formula above would only be looking at the rows containing the project numbers. 

 

From what I gather from the screenshot Frank provided, only the lowest level of hierarchy is going to contain the word "Testing", so couldn't we reference the entire column instead of having to look at a specific hierarchy level?

.

I love the LOWER addition. I have been building that into my FIND functions more and more lately. It just makes life easier.

.

And finally... There you go with those quotes again. Lol! 

Using "1" as the criteria will mean that it is looking for that very specific text. Using 1 means it will look for that number.

.

Taking the above into account and using your formula as a base, I would actually end up making the suggestion of...

 

=COUNTIFS([% Complete]:[% Complete], 1, [milestones names]:[milestones names], FIND("testing", LOWER(@cell)) > 0)

Andrée Starå

Paul,

You may not but you already did so! 

I don't agree regarding the children because Frank wanted to have them per project and it seems like it should work according to his structure.

Frank decides  

The quotes don't matter in this scenario because it will work anyway. I think I added them because 

Best,

Andrée

 

Paul Newcome

Andree,

I missed the "per project" when I first read the post. I was going for an overall tally. That's my mistake.

So you would put the formula you provided on the rows that have "Milestones project #" and replace the cell reference to "[milestones names]@row"?

 

As for the quotes... I can never figure out when they can and can't be used. I've always just used the general rule of thumb that quotes means text and no quotes means number. I didn't realize it would work in a % Complete column like that. Apparently you can even manually enter "100%" without having it formatted as a percentage, and it will still register as "1".

Thanks for putting me in my place. 

.

.

Frank,

Nevermind our back and forth. Hopefully you're able to sift through all of the mess and find a working solution. Let us know if you can't and we will see if Andree can come up with something else. Hahaha. Welcome to the Community. 

Andrée Starå

Paul,

No worries!

Best,

Andrée

FrankOrtz

Hello Gentlemen! 

Sorry for my late answer.

Thank you for your help! It was very helpful. At the end, I solved what I wanted with two different formulas. One formula counts if a particular milestone has achieved 100%, and the other formula is when the milestone has been started (>0%) but it's not completed yet. 

Formula 1= IF(COUNTIFS(CHILDREN([% Complete]1), 1, CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0)) > 0, 1)

Formula 2= IF(OR((COUNTIFS(CHILDREN([% Complete]1), <1, CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0, FIND("Development", @cell) > 0)) < SUM(COUNTIFS(CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0, FIND("Development", @cell) > 0)))), COUNTIFS(CHILDREN([% Complete]1), <1, CHILDREN([% Complete]1), >0, CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0, FIND("Development", @cell) > 0))), 1)

I tried here and in other sheets to use the LOWER(@cell) functionality but I didn't get what I was expecting. The idea is to search in all the cells of the referenced column without differentiate between uppercase and lowercase words, right? For example, I want to search for the status project : "Complete", and this should be equal to "COMPLETE". Because it didn't work properly, I created this= ...OR(FIND("Complete", @cell) > 0, FIND("COMPLETE", @cell) > 0)

Thank you so much again and have an amazing day!

 

 

 

 

Paul Newcome

Glad you were able to find a working solution. In your particular examples above you would use something like this for the LOWER function...

 

FIND("complete", LOWER(@cell)) > 0

 

What you are doing is converting the contents of each individual cell to lower case then using it in the FIND function within the rest of your formula. Just be sure that the text you use to specify what you are looking for is in all lower case as well.

Andrée Starå

No worries!

Happy to help!

Best,

Andrée

FrankOrtz

Thank you! I'll use it soon and certainly it will work well!

 

Matthew Walkup

I realize that this is an old thread, but I had a similar question that I think I figured out a novel approach. I wanted the level of the hierarchy, i.e. 0=Top, 1=Child, 2=Grandchild, etc. You can then use that to figure out the rest of your issue.

I ended up using this formula:

=IF(COUNT(PARENT()) = 0, 0, PARENT() + 1)

If you wanted to start your count at 1 (i.e., 1= Top, 2= Child, 3= Grandchild, etc...

=IF(COUNT(PARENT()) = 0, 1, PARENT() + 1)

Hope this helps someone!

Andrée Starå

Hi @Matthew Walkup

Thanks for sharing!

I usually use this one.

=IF(COUNT(CHILDREN(Task@row)) > 0; COUNT(ANCESTORS()) + 1)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!