Count Hierarchies without including dupes
Is it possible to count the number of the hierarchies?
Right now, I'm using the count function and manually clicking on level 1 for those counts and level 2 for those counts. My document is rather large and I don't want to click through each one and someone may insert a new level and I don't want a break in my formula.
The formula I used for level 2 that counts the total number of students is as follows:
=COUNT([Placement ID]2, [Placement ID]5, [Placement ID]8, [Placement ID]10, [Placement ID]13)
I didn't use the count column because I don't want to count everything there since there are dupes and I need a true count of the students. Each student can have 13 assignments which is why you see the dupes. I just want to count the level which will give me 5 students and not 15.
I hope my example makes sense.
The info is fictitious but my sheet has the same layout and I need to be able to count the cohorts and individual students, not dupes.
Any assistance is greatly appreciated.
Best Answer

Hi @jgneely72151
Hope you are fine, you can create a helper column call it "levels" and use the following formula to define each level of the Hierarchies then you can count how many of level 1 for example, and how many of level to ...atc
=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers

My apologies! I was hurrying when I created this data and just realized that I should be using the distinct function in my count formula. When I tested it, I saw my error in how i listed the names on the hierarchy. That was giving me an incorrect count. I corrected it.
Anyhow, now that I know that I can use =count(distinct(Placement ID]:[Placement ID] to return a count for me, I still need it to count the number of cohorts and number of students using the Placement ID column and I keep getting unparsable error. Again, so sorry for the error earlier but any help you can provide is most appreciated!

Hi @jgneely72151
Hope you are fine, you can create a helper column call it "levels" and use the following formula to define each level of the Hierarchies then you can count how many of level 1 for example, and how many of level to ...atc
=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

I would suggest just a text/number column with
=COUNt(ANCESTORS())
in every row.
You can then use
=COUNTIFS([Helper Column]:[Helper Column], 0)
to count the cohorts and then change the 0 to a 1 to count the students.

@Bassam.M Khalil OMG!!! Last night before going to bed, I found this solution on YouTube and was going to try it out this morning. I was thinking this will have to be the fix to my issue and you confirmed it this morning. I really, really, really appreciate you!!!! Thank you so much!

Happy to help you anytime @jgneely72151 also check the great advice by @Paul Newcome
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!