Countifs for Portfolio Summary
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!
Answers
-
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")
Sincerely,
Jacob Stey
-
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?
-
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")
Sincerely,
Jacob Stey
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!