count numbers that are in two sets of numbers

[omp001]

Hi all.

Please I need a formula to count the numbers that are simultaneously in two groups of numbers.

If a number from the first group is matched on the second and it's duplicated on the second it should be counted only once.

Please see attached sample.

Many thanks in advance.

[DILIPandey]

HI Osvaldo,

You can use below formula (which need to be confirmed with key combination ctrl shift enter) and drag it down


=MAX(--($B$9:$D$11=B3))+MAX(--($B$9:$D$11=C3))+MAX(--($B$9:$D$11=D3))



Regards,
DILIPandey

[Bob Phillips]

Try

=SUM(SIGN(COUNTIF($B$9:$D$11,B3:D3)))

it is an array formula

[omp001]

Hello boys.
Both formulae work great !
Many thanks DILI and many thanks Bob.
Have a nice Sunday.