Count Unique Entries in a range.

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
Is there anyway i can
count unique entries in the Range A1:A10
i have data which repeats but i ant to count only unique entries???
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
In an adjacent column

=IF(COUNTIF($A$1:$A$10,A1)=1,1,0)


will only return 1 when the value in A1:A10 is unique, you can then sum the 1's
 
Upvote 0
Steve,
What about the duplicate entries....
If a value is repeated thrice i want that to be counted as once.
i dont know if im clear
A1 = India
A2 = India
A3 = Australia
A4 = America
in B1 i want a formula where in it should count unique entries which means i actually have 3 entries (India is repeated twice)
 
Upvote 0
Hi Richard,

I tried ur formula (dint quiet understand though)
however doesnt fulfil my request :(
 
Upvote 0
Richard's formula is useful for Excel versions 2003 and above. You may also try

=SUMPRODUCT((A1:A10<>"")/(COUNTIF(A1:A10,A1:A10)+(A1:A10="")))
 
Upvote 0
If you don't mind array formulas, also

{=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))}
 
Upvote 0
Hey Richard, Thank you Sir. That helped me.
but id like to addon .... ;)
A1 = Rajiv
A2 = Rajiv
A3 = Richard
A4 = Steve
A5 = Rajiv
===========
B1 = India
B2 = India
B3 = UK
B4 = Australia
B5 = Australia

Now i want to know If Cell C1 = Rajiv then how many unique entries for rajiv the result should be 2.

Possible?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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