How to add NOT to existing COUNTIFS formula

Julie Sanburn

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!

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.