How can I count unique values in Excel?

Question

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: https://www.youtube.com/watch?v=kJFeQQOfu0k&ab_channel=dptutorials
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?

FlexYourData · Accepted 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.

How can I count unique values in Excel?

One Answer

Add your own answers!

Ask a Question