Countifs project status is neither complete nor cancelled

Brian Lynch

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.

Brian Lynch

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å

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

 

Brian Lynch

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.

Brian Lynch

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å

Excellent!

Nicely done!

I'm always happy to help!

Shawn Borror

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.

Hi @Shawn Borror

It looks like you posted the same question as a new post, so I've answered you there:

https://community.smartsheet.com/discussion/65355/using-countif-to-determine-dept-but-excluding-projects-that-are-hold-status

Thanks!

Genevieve