SUM(COLLECTION(...))
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
-
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
-
Try a SUMIFS instead.
=SUMIFS({range to sum}, {Type}, People$1, {Allocation}, $Label@row)
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!