Counting Unique Numbers

[cotech_10]

Hi All,

I have two columns of Data where Column 1 is the Range Data and Column 2 is the Criteria Data:

Column 1 Column 2
100 100
200 200
300 900
400 1100
500 100
600 100
700 1500



I simply would like to count the numbers in Column 2 that appear in Column 1
but not counting the duplicate appearances.

For example: Number 200 appears in both Columns 1 and 2 once only
However number 100 appears in Column 2 and Column1 but appears in
Column 2 (3) times.

Therefore the count would be (4), but I would like to count how many numbers
in column2 appear in Column 1, therefore the count should only be (2) numbers
Those numbers being Numbers 100 & 200 and not a numbers duplicate.

 

[JvdV]

Have a go with:

=SUMPRODUCT(--(COUNTIF(B1:B7,A1:A7)>0))

 

[cotech_10]

=SUMPRODUCT(--(COUNTIF(B1:B7,A1:A7)>0))

Hello JvdV,

Thank you for your reply.. But that formula does not seem to work as it is still counting the duplicates

 

[cotech_10]

Hello JvdV,

I think I have found the where the problem is. It seems in the COUNTIF function the syntax is COUNTIF(range, criteria). If I chose Column 1 as my Range and Column 2 as my Criteria I was getting duplicates being counted. However, I swapped Column 2 as my Range and Column 1 as my Criteria and no duplicates were counted and the correct number of values were counted.

Thank you for your help