complex formula?

Hi All,

Not sure if this is even possible (without vba) but thought i'd give it a go. I'm trying to perform a complex countif with a vlookup? (i think)

here's what i've got.....

a supplier number (and some other info) on one sheet...

SUP123
SUP234
SUP345 etc...

on another sheet each supplier has has a tier rating based on region, so tier 1, tier 2, tier 3....

Supplier..........London..........South..........North
SUP123..........1..................1................3
SUP234..........2..................1................2
SUP345..........3..................3................2

I want to insert 3 columns on sheet on (t1, t2, t3) which takes the supplier number then looks up the supplier on sheet 2 then counts the amount of tier 1's and if the count is greater than 1 then put a 1 in the cell. confused??

This would mean if i did a count of t1 for SUP123 i'd get a result of 2 therefore putting the number 1 in the cell. If i did the same test for SUP345 then i'd get a result of 0 (because it never has t1) so put a 0 in the cell.

This essentially is removing the regions and just letting me know if a supplier is a ever a t1, t2, t3. I've tried a few different mixes of countif with vlookup but with no joy.

Anyone got any ideas??? I can send an example if this is not clear.

Thanks, spin.