Need help using NOT in a COUNTIFS cross-sheet formula.

Mike Wilday

I think I am off my game. I cannot for the life of me figure out how to use NOT in a cross reference formula... I have a sheet with tons of data and am referencing a status column that has four options, blank, not started, in progress and complete. I want to count every row that is NOT complete. Here is what I have tried... 

=COUNTIFS({Testing Sheet Range 2}, =NOT("complete"))

=COUNTIFS({Testing Sheet Range 2}, NOT("Complete"))

=COUNTIFS({Testing Sheet Range 2}, "NOT('Complete')")

Not sure what I'm missing and why it won't give me the results I need. Please help! 

 

Paul Newcome

Try your second one with @cell = in the NOT function.

 

NOT requires a logical statement meaning This = That/This < That/This > That/etc.

 

=COUNTIFS({Testing Sheet Range 2}, NOT(@cell = "Complete"))

 

I ran a quick test with this and it worked (I assume):

 

=COUNTIFS({12 Week for Updates in Progress Range 1}, NOT(@cell = "Complete"))

 

It gave me a result of 345. It was a total of 657 lines, and I wasn't about to sit there and count them all. The point is... It gave me a number that seems like it should be accurate based on the overall status of the project plan I used for the testing (53%), so...