Counting Unique Numbers

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
135
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Have a go with:

Excel Formula:
=SUMPRODUCT(--(COUNTIF(B1:B7,A1:A7)>0))
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top