Match formula for duplicate values

Ladkisson
Ladkisson used Ask the Experts™
on
Hi! Column A1:A25000 has data. This data has duplicates. How do I count total values excluding the repeating numbers? Thanks! P.S. I have Excel 2010.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this array formula

=SUM(IF(FREQUENCY(IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""), IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""))>0,1))
Commented:
Hi!

So you have numbers in column A? And you need how many different numbers you have?
Then you can use this formula:

=SUM(--(FREQUENCY(A1:A25000,A1:A25000)>0))
confirm with Ctrl+Shift+Enter.

Cheers,
Kris

Author

Commented:
Great! Short to the point! Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial