Project Manager's Rolled up % allocation using metrics sheet

Richard Crossman

Hi there, looking for help from this group.

We currently have a master sheet where all projects live (1 row per project) and have a column for the project manager's name and a column with their manually entered allocation in percentage per project.

I would like to be able to have a metric sheet that would be able to calculate the total percentage per project manager from the allocation percentage column and allow me to show the allocation in a dashboard from a report.

I know this is probably super simple but I just can't seem to get it working.

Please help!

Paul Newcome

Are you able to provide a screenshot of what you are working with as well as a screenshot of some manually entered data that reflects what you would want the outcome of the formula to be?

Richard Crossman

Hi Paul thanks for this.

So here are the columns I have

With phase of the project, the PM and the time they are manually adding in as a percentage.

Here is the result I want

So the first column shows the PM name, 2nd percentage of total allocation of each project and the other columns count how many projects the PM has in each phase with a total at the end.

Please help! (It's probably really easy but the new metric sheets twist my melon)

Paul Newcome

Ok. For the Allocation, try this...

=SUMIFS({Data Sheet PM % Alloc Column}, {Data Sheet PM Column}, FIND(Label@row, @cell) > 0)

For the counts you would use something along the lines of this but change the bold text as needed to cover each of the different phases:

=COUNTIFS({Data Sheet Phase Column}, @cell = "Execution", {Data Sheet PM Column}, FIND(Label@row, @cell) > 0)

Richard Crossman

Both showing unparseable.

=SUMIFS({ME - GLOBAL PORTFOLIO Range 1}}, {{Project Manager}}, FIND(Label@row, @cell) > 0)

=COUNTIFS({{Project Status}}, @cell = "Execution", {{Project Manager}}, FIND(Label@row, @cell) > 0)

Paul Newcome

You should only have one set of curly brackets around each of the {Cross Sheet References}.

Richard Crossman

Argh!

SO obvious!

It works, thank you so much Paul, legend!

Paul Newcome

Happy to help. 👍️