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???
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
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)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi Rajivsoni

Did you try my formula?
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
Hi Richard,

I tried ur formula (dint quiet understand though)
however doesnt fulfil my request :(
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961

ADVERTISEMENT

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="")))
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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))}
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top