COUNTIFS

Neil Watson

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?

Neil Watson

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.

Nikhil Bhardwaj

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.

Neil Watson

@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

Neil Watson

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.