Count if

I am working on getting my Metric set up

I want to count if Project is PMO or CI and In progress, Not started ect , from my sheet called Project in take



Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Here are a few formulas to get you started.

    =COUNTIF(AND({Project in take | Project Category} = "PMO" , {Project in take | Project Status} = "Not Started"))

    =COUNTIF(AND({Project in take | Project Category} = "PMO" , {Project in take | Project Status} = "In Progress"))

    =COUNTIF(AND({Project in take | Project Category} = "CI" , {Project in take | Project Status} = "Not Started"))

    =COUNTIF(AND({Project in take | Project Category} = "CI" , {Project in take | Project Status} = "In Progress"))

    {Project in take | Project Category} and {Project in take | Project Status} would be cross sheet references you would have to setup to reference the columns from your Project in take sheet.

  • John Zeus
    John Zeus ✭✭✭

    Getting this error


    =COUNTIF(AND({Project Intake Sheet Range 6} = "PMO", {Project Intake Sheet Range 5} = "Not Started"))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Did you setup the cross sheet references?

  • John Zeus
    John Zeus ✭✭✭

    yea that's the "Project intake sheet range 6"

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    So, yeah... ignore what I said before.

    You will want to use COUNTIFS instead of COUNTIF. Give this a try.

    =COUNTIFS({Project Intake Sheet Range 6}, "PMO", {Project Intake Sheet Range 5}, "Not Started")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The COUNTIF/AND combo used above doesn't work. To include multiple range/criteria sets, you will need to use a COUNTIFS such as the last post before this one.


    You can take that and make it more dynamic so that you can dragfill by replacing "specific text" with locked in cell references. Since I can't see the column names in your screenshot, I will use [Primary Column] for the column housing the Statuses and [Current Column] for the column the formula is in. I will also assume that "PMO" is on row 1.

    =COUNTIFS({Project Intake Sheet Range 6}, [Current Column]$1, {Project Intake Sheet Range 5}, $[Primary Column]@row)


    If you drop that in the top left corner of the table that is to house numbers, you can dragfill to the right and down to quickly populate the rest of the table instead of having to update each formula individually for each variable.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!