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

Mike Wilday
Mike Wilday ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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
    Paul Newcome ✭✭✭✭✭✭
    edited 10/18/18

    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... laugh

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!