Countifs for Portfolio Summary

lindseyb

Hello! I am trying to do a portfolio summary for my PMO. I want to count the number of active projects in each project stage, for example, active projects in the planning stage.

I have a sheet that has a column for if the project is active and another for the project stage, but I don't know how to structure the formula.

Help please!

SteyJ

Your formula would look something like this to count Active projects in Planning Stage.

=COUNTIFS([Active Projects]:[Active Projects], 
[Project Status]:[Project Status], "Active", 
[Project Stage]:[Project Stage], "Planning")

lindseyb

I tried this =COUNTIFS({Project Intake Sheet Range 2}:{Project Intake Sheet Range 2},{Project Status}:{Project Status},"In Progress",{Project Intake Sheet Range 3}:{Project Intake Sheet Range 3},"Not Started") but am getting the #UNPARSEABLE error. Any suggestions?

SteyJ

When you're using cross sheet references, you only need to reference it once, however I'm pretty sure you need to include CONTAINS so you can check the cell at which it should be counting instead of all of the cells at once.

Here is what the formula looks like when doing cross sheet references:

=COUNTIFS({Project Status}, CONTAINS("In Progress", @cell), 
{Project Intake Sheet Range 3}, CONTAINS("Not Started", @cell))

Turns out not using CONTAINS will also work:

=COUNTIFS({Project Status}, "In Progress", 
{Project Intake Sheet Range 3}, "Not Started")