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.

Find more posts tagged with

Formulas

Reports

Services

Marketing

Comments

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

Sign in to join the conversation!

Trending discussions

How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…