Count Hierarchies without including dupes

jgneely72151 · 01/31/21

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 · 02/01/21

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)

jgneely72151 · 01/31/21

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 · 02/01/21

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)

Paul Newcome · 02/01/21

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 · 02/01/21

@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 · 02/02/21

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

Formulas and Functions

Formulas and Functions

Count Hierarchies without including dupes

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of values in a multi-select dropdown list

How would I write a formula to locate duplicates in 1 column and the difference in another column.

#NO MATCH result but not for identical formulas