Count cells where content doesn't match a valid list elsewhere

[H3MSY]

Morning all, I have used the search function but cant find the answer i need...

I have a column (B:B) full of user input values that has data validation on it to only allow a user to select a value from "validation" list elsewhere in the Workbook. The problem is the user can still paste values from other places into the cells in B:B and this could be a value not within the "validation" list and we would never know.

I want to put a check within the workbook, that counts how many cells in B:B with a value have a value not listed in the validation list. - if its zero then all good, but if its 1 or above they need to run a check on B:B

Hopefully this makes sense and its something that can be done with a formula rather than vba

 

[H3MSY]

I think i might have found a solution. I could use MATCH to count how many cells matched the validation list but i couldnt work out if there is a function is the reverse of MATCH ie Does not match.. so I've come up with this

=COUNTA(B:B)-COUNT(MATCH(B:B,validationlist,0))

probably a better way but this seems to work

 

[Fluff]

Try

=COUNTA(B:B)-sum(COUNTifs(B:B,validationlist))