Count unique values in a range

[vij8y]

Hi There,

I have a range which is filled with different City names. There are totally 10,000 City names and only 220 are unique entries.

I want a formula which will help me count the number of unique values in a range.

Please help.

Thanks in advance.

VJ

[thameem127]

Use this formula.

COUNTIF(A2:A100,A2:A100))

[vij8y]

Sample File.xlsxSample File.xlsxThank you thameem127.

I have attached my sample file and the answer I am expecting is 3 as there are only 3 unique values.

Can you tweak your formula by looking at my sample file.

Thanks,
VJ

[thameem127]

Try this attached file.Copy of Sample File coun if.xlsx

[jenny_fmst]

Hi there, I've been working on the same question. This is best solution I've come across so far, found here at office.microsoft.com

I feel like there must be a simpler solution, so I'll be watching to see if one pops up

applied to your sample file:
Copy of Sample File-1.xlsx

This is how I've used it:

=SUM(IF(FREQUENCY(IF(LEN(B4:B4000)>0,MATCH(B4:B4000,B4:B4000,0),""), IF(LEN(B4:B4000)>0,MATCH(B4:B4000,B4:B4000,0),""))>0,1))

It is an array formula, so be sure to hit cntrl+shift+enter

jenny

[FDibbins]

try this....
=SUMPRODUCT((A2:A200<>"")/COUNTIF(A2:A200,A2:A200&""))

[martindwilson]

=COUNTIF(A2:A101,A2:A101) is exactly the same result as =COUNTIF(A2:A101,A2)

[vij8y]

Thank you All for your kind help and time. This helped and resolved.