Need better counting formula

[Alan C]

I have been using this formula to find the number of unique entries in a column: =SUM(IF(LEN(RANGE),1/COUNTIF(RANGE,RANGE))), Control,Shift,Enter – RANGE being the named range I’m trying to count. No other conditions, just the count of unique in a specific column. When I get to very large data sets, the formula freezes my computer and I have to close Excel. Large = 50,000 items in column.

Is there a more efficient formula that will count unique items in a column without breaking the bank, crashing the system?

Thanks for any help.

Alan

 

[jim may]

How's this?

=COUNT(1/FREQUENCY(A:A,A:A))

 

[BrandonWLH]

Advanced filters. Select unique records. Copy to a new location and then just CountA

jim beat me..

 

[Alan C]

Thanks, Jim, will give it a try now.

 

[Alan C]

Thanks to you too, Brandon. Jim's simple formula sure is peppy! Two seconds and ran 70,000 plus rows in specific column. Many thanks to all!

Alan