COUNTIFS with NOT Logic

Hello everyone,

I have a sheet that summarizes the data across our portfolio. There are three columns I want to use in my COUNTIFS function: Core Assignment, SLT Flag, Status. I want to count all the projects that equal the core name, have the flagged checked, and status is NOT in Completed, Cancelled, or On Hold.

The formulas I am using are located on a separate sheet that I use to calculate metrics. The formula I am using is:

=COUNTIFS({Core Assignment}, [Core Name]@row, {SLT Flag}, 1, {Status}, OR(@cell <> "Cancelled", @cell <> "Completed", @cell <> "On Hold"))

The issue I am experiencing is that rows with a project status of completed are being picked up and counted. The dataset I am practicing with has 8 total projects, 7 of which are 'On Track' and 1 is 'Completed.' When I run the above formula, it is returning 8 so I think the <> logic isn't working correctly.

Find more posts tagged with

Formulas

Accepted answers

Linda Manduchova

Hello @James Tontarski -

Try to change your "OR" function to the "AND" function and your formula should return back your desired result. Hence, the formula will look the following:

=COUNTIFS({Core Assignment}, [Core Name]@row, {SLT Flag}, 1, {Status}, AND(@cell <> "Cancelled", @cell <> "Completed", @cell <> "On Hold"))

Linda

All comments

Linda Manduchova

Hello @James Tontarski -

Try to change your "OR" function to the "AND" function and your formula should return back your desired result. Hence, the formula will look the following:

=COUNTIFS({Core Assignment}, [Core Name]@row, {SLT Flag}, 1, {Status}, AND(@cell <> "Cancelled", @cell <> "Completed", @cell <> "On Hold"))

Linda

James Tontarski

Thanks for that Linda.

Oddly enough, when I first updated the formula with your answer I was still getting incorrect data. Out of frustration, I deleted the columns and removed all sheet references. When I recreated the columns and re-added the references the formula worked.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.