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

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    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

  • Nikhil Bhardwaj
    Nikhil Bhardwaj ✭✭✭
    edited 11/22/20

    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
    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
    Neil Watson ✭✭✭✭✭✭
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!