How to add NOT to existing COUNTIFS formula

How do I add a condition of NOT to my existing Countifs formula. I want to exclude projects that are "Complete", "Paused", "Cancelled" per the Project Phase. This status is in my existing sheet "NBS Project Tracker."

Current working formula:

=COUNTIFS({NBS Project Tracker Project Complexity}, "High", {NBS Project Tracker ET Area}, "Business Development")

Updated not working formula:

=COUNTIFS({NBS Project Tracker Project Complexity}, "High", {NBS Project Tracker ET Area}, "Business Development", NOT({NBS Project Tracker Project Phase}, "Complete", "Paused", "Cancelled"))

I appreciate the help!

Find more posts tagged with

Accepted answers

All comments

Melissa Yamada

Hello @Julie Sanburn

Try this:

=COUNTIFS({NBS Project Tracker Project Complexity}, "High", {NBS Project Tracker ET Area}, "Business Development", {NBS Project Tracker Project Phase}, AND(@cell<>"Complete",@cell<>"Paused", @cell<>"Cancelled"))

Julie Sanburn

@Melissa Yamada
Thanks for your help. I get #invalid data type error message. Maybe I have unnecessary spaces?

I appreciate any guidance you can provide. Thank you!

Paul Newcome

You have an extra comma that needs removed immediately after the first @cell reference.

Melissa Yamada

Hello @Julie Sanburn

Paul's right there is an excess "," there.

It should be:

AND(@cell<>"Complete"…

Julie Sanburn

Hi @Melissa Yamada I could not get the formula to work as it turns out COUNTIFS cannot have any functions inside it, only a list of ranges and conditions. I used the following formula and got it to work.

=COUNTIFS({NBS Project Tracker Project Complexity}, "High",
          {NBS Project Tracker ET Area}, "Business Development",
          {NBS Project Tracker Project Phase}, "<>Complete",
          {NBS Project Tracker Project Phase}, "<>Paused",
          {NBS Project Tracker Project Phase}, "<>Cancelled")

Paul Newcome

@Julie Sanburn I use the AND and OR functions inside of COUNTIFS almost daily. Your last COUNTIFS with the AND function should have worked if you just removed that comma after the first @cell.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.