COUNTIFS with Two NOT CONTAINS for the same Column
I need a formula to return the count of the number of rows where my Receipt # column is not blank, and where the Notes column does not contain insufficient funds and does not contain preauthorized payment. I've gotten the formula to work for the first two criteria, but I can't get it to work when I try to add the criteria of the additional check for the Notes column to not contain preauthorized payment.
Here is the formula I have:
=COUNTIFS([Receipt #]:[Receipt #], <>"", Notes:Notes, NOT(CONTAINS("insufficient funds", @cell)), Notes:Notes, NOT(CONTAINS(“preauthorized payment”,@cell)))
It's the second "NOT(CONTAINS" that doesn't work, I have gotten it to work up to then, so I'm not sure if I should be grouping the "preauthorized payment" exclusion with the first NOT(CONTAINS statement, or if I should have it separate like I do in this formula but am just not doing it correctly.
Answers

I tried to replicate what you are describing and got this to work:
=COUNTIFS(Notes:Notes, <>"insufficient funds", Notes:Notes, <>"preauthorized payment", [Receipt #]:[Receipt #], <>"")
This won't work, however, if the notes column has more than just a single value. If you need to use the contain function due to notes having more than a single value try this:
=COUNTIFS(Notes:Notes, NOT(CONTAINS("insufficient funds", @cell)), Notes:Notes, NOT(CONTAINS("preauthorized payment", @cell)), [Receipt #]:[Receipt #], <>"")
I know this looks pretty much identical to what you had, but I got an error when I had the column receipt # first and it worked when that was moved to the end. I don't have a good explanation for why that is, but again it worked on my end with some dummy data to try to replicate your situation.

The second one worked, thank you so much! I hadn't realized I needed to make sure the order was correct for my other formulas in the sheet, so I'm swapping them to have Notes first (because it could have multiple values) and it seems to be working just fine that way. Thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!