SUM(COLLECTION(...))

Paul Reeves

I am using the Project Management Office template set and trying to tweak the Portfolio Metrics worksheet to track people resources, percentage wise, across multiple projects.

In reviewing the formulas they all appear to be collecting data from the Intake worksheet. I created a new section in the metrics for Resource Allocation (Assigned To) and listed the names of individuals. Tweaking the formula I am able to count the number of projects for each individual and Type, a.k.a. Category. A project leader may be managing multiple projects across multiple types, categories.

=COUNT(COLLECT({Prj ID}, {Type}, People$1, {Assigned To}, $Label@row))

I also added a new column in the Intake for the resource allocation, Allocation. Now I would like to pull in the Resource Allocation percentage for each individual and Type. But it seems I am missing something.

=SUM(COLLECT({Prj ID}, {Type}, People$1, {Allocation}, $Label@row))

Any help is greatly appreciated.

Paul Reeves

After reading and working through Collection function a few times I realized my range was not correct. Once I changed it to Allocation and brought in the Assigned To everything worked.

=SUM(COLLECT({Allocation}, {Type}, People$1, {Assigned To}, $Label@row))

Paul Newcome

Try a SUMIFS instead.

=SUMIFS({range to sum}, {Type}, People$1, {Allocation}, $Label@row)

Paul Reeves

After reading and working through Collection function a few times I realized my range was not correct. Once I changed it to Allocation and brought in the Assigned To everything worked.

=SUM(COLLECT({Allocation}, {Type}, People$1, {Assigned To}, $Label@row))