COUNTIFS
Not sure what I am doing wrong with what seems like a basic formula...
I have a sheet which contains a list of projects  project owner, status etc. I am trying to create a separate summary sheet/table that consolidates the total number of projects for each PM, and subsequent columns the status of each project. I am using COUNTIF referencing the project sheet, the "At Risk" column is below:
=COUNTIFS({Capital Work In Progress Range 3}, [Project Owner]@row, [{Capital Work In Progress Range 3},"At Risk"]) etc.
What am I doing wrong?
Best Answer

Perhaps more detail will highlight what is wrong
The summary sheet is extracted below, and has various column formulas depending on the project status
Independently, each "Range, Criterion" condition works as intended, but when both are detailed the formula returns a zero
The Countif formula points to an external reference sheet where the 2 columns below are set up as {Capital Work In Progress}. I know the external sheet reference works since as mentioned above each "Range, Criterion" returns the correct answer independently.
The "Total Projects" column is working fine (7 projects) but the balance are all zero despite the dataset above.
Answers

Hi Neil,
I have an entirely different solution on how you can show the summary. But for the formula, I think you are missing a reference. "COUNTIFS({Capital Work In Progress Range 3}, [Project Owner]@row, [{Capital Work In Progress Range 3},********* = "At Risk"])".
In the first instance, you are probably choosing a reference from different sheet ({Capital Work In Progress Range 3}) and then selected a name in a cell ([Project Owner]@row). In the second instance you added a text ("At Risk"). So u need to mention what column should have a value that is equal to "At Risk". Above where I have added "********" is where u need to put the reference column and then the equal sign and then "At Risk". I guess that should solve the problem in the formula. Hope it helps.

@Nikhil Bhardwaj still having issues with the formula, however perhaps I can try your solution (which is probably much better as I am a new user!).
Thanks

Perhaps more detail will highlight what is wrong
The summary sheet is extracted below, and has various column formulas depending on the project status
Independently, each "Range, Criterion" condition works as intended, but when both are detailed the formula returns a zero
The Countif formula points to an external reference sheet where the 2 columns below are set up as {Capital Work In Progress}. I know the external sheet reference works since as mentioned above each "Range, Criterion" returns the correct answer independently.
The "Total Projects" column is working fine (7 projects) but the balance are all zero despite the dataset above.
Help Article Resources
Categories
Check out the Formula Handbook template!