For a Project Portfolio Roll Up, SUM of Total Budget IF Project is Active/In Progress

Alec Jessee

I'm using the Project Management Office template set; however, it does not come with Budget totals, health, etc., like it does for the Schedule. I had no problem adding budget info for individual projects and displaying them on the project dashboard. I am now trying to get this information to roll up to the Portfolio level and dashboard.

On the Portfolio Metrics sheet (used for many of the Portfolio Dashboard numbers/charts; shown below), there is a formula to count the total number of active and all-time projects based on schedule health (green, yellow, red) and project type (large, medium, small). It pulls this information from the Project Intake sheet (shown below). For example, to count the total number of active projects with a green schedule health:

=COUNT(COLLECT({Project ID}, {Project Status}, "In Progress", {Schedule Health}, $Label@row))

I am looking to do something similar with the budget numbers I added. But instead of a total COUNT, I want to do a total SUM, IF the Project Status is Active (In Progress). I have not been able to figure out how to make that work, if it's possible. Any advice is appreciated.

Ryan Sides

@Alec Jessee

Hi Alec, if you're looking to sum all projects in the "Active" status:

1) you'll need to right click in your metric sheet and click on Manage References.

2) create a reference to the Total Budget column on your Intake Sheet. Name this reference "Intake Total Budget". Make sure you click the column's header, not an individual cell.

3) In the cell to the right of your Total Planned Budget Active cell, use this formula

=sumif({Project Status}, <> "Complete", {Intake Total Budget})

You could also use SUMIFS if you have multiple statuses you want to exclude.

Does that work for you?

Ryan

Ryan Sides

@Alec Jessee

Hi Alec, if you're looking to sum all projects in the "Active" status:

1) you'll need to right click in your metric sheet and click on Manage References.

2) create a reference to the Total Budget column on your Intake Sheet. Name this reference "Intake Total Budget". Make sure you click the column's header, not an individual cell.

3) In the cell to the right of your Total Planned Budget Active cell, use this formula

=sumif({Project Status}, <> "Complete", {Intake Total Budget})

You could also use SUMIFS if you have multiple statuses you want to exclude.

Does that work for you?

Ryan

Alec Jessee

@Ryan Sides, thank you. I was missing the create a reference step. I'm still messing around with this new information, but I'm curious, why would it be "Complete" for Project Status, and not "In Progress"? The other statuses a project can be include: On Hold, Canceled, and Not Started.

It also looks like if I want to sum the total budget of all projects regardless of status, I can use =SUM({Intake Total Budget})

Again, much appreciated.

Ryan Sides

No problem!

The <> in front of Complete means when it's not equal to Complete; stated differently when status is anything other than Complete

Alec Jessee

@Ryan Sides , I am going to create a new post shortly, but I thought you might be able to help. Considering the above information, I am now trying to show metrics for individual project managers in addition to the same metrics above. I was able to develop successful formulas for COUNT, but not SUMIFS. To do this, I believe I need both a column for an individual project manager, and a row:

I created a reference to the Intake form for the PM column and called it PM Projects. Example of a successful formulas for Counts for Bob: =COUNT(COLLECT({Project ID}, {PM Projects}, "Robert Goodwin", {Project Category}, Large$1))

and Project Schedule Health (Active) for each color: =COUNT(COLLECT({Project ID}, {Project Status}, "In Progress", {Schedule Health}, $Label@row, {PM Projects}, "Robert Goodwin"))

For the budget, I tried several variations/placements of: {PM Projects}, "Robert Goodwin" in the current formula: =SUMIF({Project Status}, ="In Progress", {Intake Total Budget}). I did use SUMIFS. One failed example: =SUMIFS({Project Status}, ="In Progress", {PM Projects}, "Robert Goodwin", {Intake Total Budget})

Do you know what I'm missing?

Ryan Sides

Hi Alec! Glad to hear from you and I'm glad to hear you're making progress!

You were SO close! The SUMIFS formula was the way to go, but you're missing the first reference which is "what column do you want to sum when the other columns find a match" (I bolded it below). So maybe your planned budget for Bob would look like this...

=SUMIFS({Planned Budget}, {Project Status}, ="In Progress", {PM Projects}, "Robert Goodwin")

Note that I used the reference named planned budget, but if this value is in the Intake Total Budget reference, use that instead like this: =SUMIFS({Intake Total Budget}, {Project Status}, ="In Progress", {PM Projects}, "Robert Goodwin")

Does that work for you?

Ryan

Alec Jessee

@Ryan Sides , it worked! Thanks again.

Ryan Sides

@Alec Jessee happy to help!