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...
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.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
