JOIN / SUM / DISTINCT / COLLECT

dannyh

Hello,

We have a sheet for tracking projections for project managers on multiple projects.

I'm trying to develop a formula to return a list of project managers who have less than a certain amount of hours per month.

I can do a formula to calculate how many hours that PM on that row has for that period, but I can't figure out how to do a JOIN function to concactenate them.

If I do:

=SUM(DISTINCT(COLLECT([10/23]:[10/23], [Project Manager]:[Project Manager], [Project Manager]@row)))

I get how many hours that PM has for all their projects for that period which I could do a JOIN(DISTINCT() function on a separate tab, but it'd take a ton of additional columns.

I think I'm looking for something like:

=JOIN(DISTINCT(COLLECT([Project Manager]:[Project Manager], SUM(DISTINCT(COLLECT([10/23]:[10/23], [Project Manager]:[Project Manager], [Project Manager]:[Project Manager]))), < 30)), ", ")

that will return a list of Project Managers that have <30 hours for that month/period.

Any thoughts how I get there?

Genevieve P.

Hey @dannyh

I can't think of a way to do that directly in the same formula, since you're looking on a row-by-row basis.

You could have another column to reference your current formula as a range:

=JOIN(DISTINCT(COLLECT([Project Manager]:[Project Manager], [10/23]:[10/23], < 30)), ", ")

Or what I would suggest is to use a Report to Filter rows based on that value, the Group by the Project Manager.

Let me know if that would work for you!

Genevieve