How can I take a range (a1:a100) that ISN'T sorted and find the number of unique entries?
A Alan Aug 1, 2008 #1 How can I take a range (a1:a100) that ISN'T sorted and find the number of unique entries?
J John C Aug 1, 2008 #2 =SUM(IF(COUNTIF($A$1:$A$100,$A$1:$A$100)>1,0,1)) This is an Array** formula. Press CTRL+SHIFT+Enter to commit the formula to the cell. This is assuming data is in all 100 cells.
=SUM(IF(COUNTIF($A$1:$A$100,$A$1:$A$100)>1,0,1)) This is an Array** formula. Press CTRL+SHIFT+Enter to commit the formula to the cell. This is assuming data is in all 100 cells.
D Dave Peterson Aug 1, 2008 #4 You can use a formula like: =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")) to count the number of unique entries in A1:A10. You may want to look at Chip Pearson's site. He has lots of techniques to work with duplicates: http://www.cpearson.com/excel/Duplicates.aspx
You can use a formula like: =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")) to count the number of unique entries in A1:A10. You may want to look at Chip Pearson's site. He has lots of techniques to work with duplicates: http://www.cpearson.com/excel/Duplicates.aspx
