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!


  • SteyJ
    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")

    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?

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 02/15/24

    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")


    Jacob Stey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!