COUNTIFS with NOT Logic

James Tontarski

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.

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

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.