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
-
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.
-
Getting this error
=COUNTIF(AND({Project Intake Sheet Range 6} = "PMO", {Project Intake Sheet Range 5} = "Not Started"))
-
Did you setup the cross sheet references?
-
yea that's the "Project intake sheet range 6"
-
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")
-
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
Categories
Check out the Formula Handbook template!
