How can I count unique values in Excel?

I have these tables:

Table 1:

Table 2:

The column 'Acertos' (the last one) in the table 1 counts how many values of the table 2 contains in it, in other words, when I add a number into the table 2, the column 'Acertos' will check to see if such number exists in the range B-H.

I am using this formula: =SUM(COUNTIF($O$2:$T$6,C2:H2))

It sort of does what I want, but it also counts the repeated values. As you can see, Ana only hit 2 and 4, so, Acertos should be 2, but since there are three 4 and two 2 in the table 2, Acertos is counting 5.

I found this tutorial:

It counts only unique numbers, but it only checks the table based on the numbers that still are in there, I need to check it based on another table.

I've tried to make some adaptations but without any success, since I am new on Excel and I am a bit lost.

How can I do that?

1 Answer

You can use this formula in Table 1:

=SUM(INT(COUNTIF($O$2:$T$6,Table1[@[Num 1]:[Num 6]])>0)) 

Your original COUNTIF gives this:

Making it "unique", you just test if each cell in the array is greater than zero, like this:

Convert it back into 1 and 0:

And then sum to give the final formula.