SUM(COLLECTION(...))

Paul Reeves
Paul Reeves Overachievers
edited 04/05/21 in Formulas and Functions

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 e. reeves

Principal Business Analyst

HMH

Best Answer

  • Paul Reeves
    Paul Reeves Overachievers
    Answer ✓

    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 e. reeves

    Principal Business Analyst

    HMH

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!