COUNTIF formula excluding large list of values from count

[Tupelo1984]

Dear, I am looking for a formula where I can use countif excluding a large list of values.

Example in attach: Tab results shows in result 0 if in the value column one of the characters appears from my 'lookup exclusions' tab. If the characters doesn't appear in my lookup list it should return 1.

In reality the lookup list and data set is a lot bigger - this is just an example.

Thank you!
Tupelo

[wk9128]

worksheet name : Results

Cell B2 array formula , Drag down

[Tupelo1984]

Hi wk9128! Thank you so much this works! I actually forgot to add an additional requirement...Hope you can help me with this.
Apart from the lookup table I also wish the count to return 0 is the value cell only consists of one character (such as A,B,C...), and also would like to score 0 for a list of values missing.

I attached the new file with similar data, including the:

- original 'exclusions table' = to return 0 if this character is in the cell
- new 'inclusions table' = to return 0 if this character is missing in the cell

And then i would need the additional condition to return 0 if there is only one character in the cell.

I hope I explain well...thank you so much for all your help.

[JeteMc]

The following modification to wk9128's array formula** replicates the values in column B:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.