Count Hierarchies without including dupes

jgneely72151

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 1-3 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.

Bassam Khalil

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([email protected])) > 0, COUNT(ANCESTORS()) + 1)

jgneely72151

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!

Bassam Khalil

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([email protected])) > 0, COUNT(ANCESTORS()) + 1)

Paul Newcome

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.

jgneely72151

@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!

Bassam Khalil

Happy to help you anytime @jgneely72151 also check the great advice by @Paul Newcome