Counting Unique Values Under Parent Rows

kelceyg

My sheet current has four project types (parent row.) Underneath each project type is the project name (children.) And underneath each project is a list of the team members working on that project (ancestors.)

I need to be able to count each unique employee working on projects under each Project Type. So if an employee is working on two project for HR Operations and one project for Strategic Partnerships, they are counted once for each type. Essentially, what I want my formula to saw is "If the parent row is HR Operations, count the unique employees"

The formula I currently have is:

=IF(COUNT(DISTINCT([Project Team]$13:[Project Team]@row)) <> COUNT(DISTINCT([Project Team]$13:[Project Team]13)), COUNT(DISTINCT([Project Team]$13:[Project Team]@row)))

However, this only counts the employee once for the entire sheet. In the screenshots below, I need the employees under Strategic Partnerships to be counted again. They are currently not being counted because they are on projects under HR Operations.

Jeff Reisman

The formula still works, just move it down a level to the individual projects under each project type.

Jeff Reisman

Place this on your "Project Type" - level row (or reference the Project Team cell from the "Project Type"-level row.

=COUNT(DISTINCT(DESCENDANTS([Project Team]@row)))

This will count unique project team member names from under all the projects under HR Operations, each name only once.

Use the formula again with the Strategic Partnerships row, you get a count from under that.

Screenshot from my test sheet:

kelceyg

Hi Jeff,

Thanks for replying! I do reference the employees in another sheet that totals their allocated hours for each project type, so I need for each employee to be assigned a number in the first column on the left.

The formula used in the allocation sheet is:=VLOOKUP([Row ID]@row, {Strategic Partnerships Range 13}, 5, false)

Note: I realize in the screenshot that the formula is still referencing column 4 when it should say 5 - this is just a copy of the original sheet so I can figure out the formula situation without ruining the sheet.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320178#Comment_320178

@kelceyg

Sorry I am just seeing this reply...

The formula I gave you works for the question/screenshot in your initial post.

However, I have no idea what you're asking in your reply. What does this even mean in relation to counting distinct team members for each project?

kelceyg

@Jeff Reisman

I apologize for the confusion; I am brand new to Smartsheet and have been left to teach myself...

In the original screenshot I sent, we have four project types: HR Operations, Learning, Strategic Partnerships, and Employee Experience. A project lead must add their project under the correct type. They must then add their team under the project name and list out the expected amount of time each team member will spend working on the project for each month.

The four project types used to have their own sheets, but I was tasked with creating one large master sheet to condense all of the information into a single place. On the original separate sheets, we used a formula to count each unique team member to feed into an allocation sheet. Each project type had a separate allocation sheet. The goal is to list all of the team members working on projects under that project type and put the total number of hours they are putting in. If a team member is over-allocated, it will flag their name.

The issue I am having is that now that the four project types are on one master sheet, I am unable to correctly pull data into the four separate allocation sheets. I believe the issue is that the team members are now being seen as unique for the entire sheet vs per project type.

For example: If Jon Smith is working on one HR Operations project and one Learning project, he SHOULD be a unique value under each project type. Instead he is only counted as unique under HR Operations - thus his info won't pull into the Learning allocation sheet.

I think that the ideal solution would be to be able to count the unique team members under each parent row (project type.) Then I can roll up each team member's allotted hours into each project type's allocation sheet.

Jeff Reisman

The formula still works, just move it down a level to the individual projects under each project type.