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

dannyh

Hi Genevieve, thanks for the suggestion but that formula only looks for individual projects that have a value less than 30. I need it to sum all the hours for the period.

A report/filter won't work either because any filters/sorts would be looking at individual line items, not the sums.

I've thought about creating a separate reference column, but I'd have to repeat that hundreds of times given the magnitude of the thing I'm working on so not really an option either but appreciate the suggestion