Countifs project status is neither complete nor cancelled

I'm new to this, and I'm having issues with counting the number of projects where Project Type = X and Project Status does Not equal Complete or Cancelled. I'm fumbling my way through unsuccessfully. This will go on a dashboard where I have other working formulas, but this is out of my expertise.

Here's what I have so far. I'm sure there are obvious errors in it, but hopefully I can get not just the correct formula, but a little coaching/suggestions as well. I don't want to be the guy who constantly leans on others.

=COUNTIFS({Project Status}, NOT(OR(@cell = "Complete", @cell = "Cancelled")), {Project Type}, Label13)

Any pointers would be much appreciated.

Comments

  • Getting closer, but still returning 0 as the value when there should be larger numbers.

     

    =COUNTIFS({Project Type}, Label17, {Project Status}, OR("Not Started", "Assigned", "In Progress", "Awaiting Content", "Awaiting Approval", "Final Delivery In Progress", "On Hold"))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Brian,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold


     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thank you very much. Here's a summary, and I'll try not to ramble.

    I'm setting up a Metrics sheet to feed a dashboard. Attached image shows two of the many tables I'm setting up. 'Label' column in the attached image has the options I wish to count. The 'All Projects' column formula works, counting all historic projects from the source sheet. For the 'Current Projects' column, I want to use the same logic, but exclude any project rows where Product Status equals either Cancelled or Complete.

    The first table 'Projects by Status' was easy enough, as I could simply delete the formula from the Complete and Cancelled rows. However, for my other tables, I'll need to add logic to exclude those rows to their formulas. The 'Projects by Deliverable' table has a simple formula of "=COUNTIF({Project Type}, Label13)" where Label13 in my case is 'Advertisement'.

    I'd like to add logic to the formula for my 'Current Projects' column that excludes everything that's completed and cancelled.

    I really appreciate the assistance.

    smartsheet-metrics.jpg

  • Figured it out!

    =COUNTIFS({Project Type}, Label21, {Project Status}, NOT(OR(@cell = "Complete", @cell = "Cancelled")))

    Seems so simple now. I appreciate your help Andrée.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    Nicely done!

    I'm always happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Help - I have the same type of issue but can't seem to get this to work.

    Essentially I want to count all projects by dept but only if they are not in a status of 'Hold'. This is what I have tried.

    Which brings me the lovely unparseable

    =COUNTIFs(Portfolio Rollups Range 1, "Commercial Ops"), NOT({Portfolio Rollups Range 8}="Hold"))

    I have also tried this formula -

    =COUNTIFS(Portfolio Rollups Range 1, "Corp IT",[{Portfolio Rollups Range 8}, <>"Hold"])

    Which doesn't work either.

  • Genevieve P.
    Genevieve P. Employee Admin

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!