How to count distinct values in Excel

Hello Everyone,

I have a sheet contains 30,000 rows. I need to determine the number of distinct (unique) values in column A. What is the most efficient way to do this in Excel?

For example, I can pull the data into Access and use a select query to "group by" column A to get the answer. But I need to get the answer within
Excel.


Thanks
Arpita

Data > remove duplicates

Pivot Table would be an easy option!

You can select all the items in column A, then click Data | Advanced on the Sort & Filter group, then in the dialogue box click against Copy to another location, type the first cell in the Copy to box (e.g. M1), click against Unique Records only, then click OK.

Hope this helps.

Pete

Are you looking to count the unique values (values that occur only once) or the distinct number of values (the total number of different values in the data)?

For the first: =IF(COUNTIF($A$1:$A$30000,A1)=1,1,0) and then sum
For the second: =IF(COUNTIF($A$1:A30000,A1)>1,0,1) and then sum

Although pivot tables are the easiest option for both.

I am really happy to see the lot of responses and ways to do one thing in Excel and I really appreciate this forum where we can learn Excel from commendable users.

Thanks everyone.