COUNTIFS with Two NOT CONTAINS for the same Column

Bethany Garcia

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 pre-authorized 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 pre-authorized payment.

Here is the formula I have:

=COUNTIFS([Receipt #]:[Receipt #], <>"", Notes:Notes, NOT(CONTAINS("insufficient funds", @cell)), Notes:Notes, NOT(CONTAINS(“pre-authorized 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 "pre-authorized 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.

Danielle O'Connell

I tried to replicate what you are describing and got this to work:

=COUNTIFS(Notes:Notes, <>"insufficient funds", Notes:Notes, <>"pre-authorized 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("pre-authorized 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.

Bethany Garcia

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!