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!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…