Excel - How to Count Unique Values among Duplicates

Environment

• Microsoft Excel 2003, 2007

Introduction
Wanna count unique values among duplicates


Solutions
Count the number of unique number
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)

Count the number of unique number and text
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

Count the number of unique number and text exclude blank cells
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))


Reference

• http://office.microsoft.com/en-us/excel-help/count-unique-values-among-duplicates-HP010070481.aspx