Count uniques matched on range, but if match reference is twice, count twice

Hi,

I don't know how to figure it out the right formula, i thnk is complex what i need to solve,
First let me explain the first ecenareo :
I have numbers to be match (A1:E1)
Need to count how many Uniques numbers from (A1:E1) are in Range ( A2:E4) Regardless if range have multiples matched duplicated.

Example 1

211 222 201 200 122 <-A1:E1 -- number to be match and count on Range A2:E4)

121 211 302 100 210 <- range
110 200 200 110 212 <- range
101 212 203 201 211 <- range

I use Countif nested, but give a wrong output ---> 5
and i use Sumproduct/Countif, w wrong output --> 5

Desired Output is -----------------------------------> 4
This is why
(A1) 211 is 1 time (matched in range)
(B1) 222 is 1 time (NOT match in range)
(C1) 201 is 1 time (matched in range)
(D1) 200 is 1 time (matched in range)
(E1) 122 is 1 time (NOT match in Range )

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Example 2:

Note that Numbers to be match and count 200 is twice, also is twice in the Range

211 200 201 200 122 <- number to match/count

121 211 302 100 210 <- range
110 200 200 110 212 <- range
101 212 203 201 211 <- range

The Desired Outpout here need to be -> 4
This is why
(A1) 211 is 1 time (2 times in range but count as unique B/C is only 1 time in A1)
(B1) 200 is 2 time (2 times in range AND count twice B/C is in B1 + D1)
(C1) 201 is 1 time (1 time in range and 1 time in C1)
(D1) 200
(E1) 122 is 1 time (is NOT in Range, it doesn't match, not count )


Thanks,

Going to work now.. have a great day !!

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

That would help.

I am now going to tidy up this thread by deleting the clutter above this.

Administrative Note:

We don't expect you to bump threads more than once in 24 hours - it has been only a few hours since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.