R Rick Sep 25, 2003 #1 In Excel 2000 I would like to count the unique entries in a range of data. Any ideas?
J Jason Morin Sep 26, 2003 #2 One way: =SUM(1/COUNTIF(A1:A10,A1:A10)) Array-entered. HTH Jason Atlanta, GA
P Peo Sjoblom Sep 26, 2003 #3 =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) or if there can be empty cells involved =SUM(IF(LEN(A1:A10)>0,1/COUNTIF(A1:A10,A1:A10))) entered with ctrl + shift & enter
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) or if there can be empty cells involved =SUM(IF(LEN(A1:A10)>0,1/COUNTIF(A1:A10,A1:A10))) entered with ctrl + shift & enter
H Harlan Grove Sep 26, 2003 #4 ... ... or if there can be empty cells involved =SUM(IF(LEN(A1:A10)>0,1/COUNTIF(A1:A10,A1:A10))) entered with ctrl + shift & enter Click to expand... ... Picky: array-entry unnecessary. =SUMPRODUCT((A1:A10<>"")/(COUNTIF(A1:A10,A1:A10)+(A1:A10=""))) Less picky: A1:A10<>"" is likely more efficient than LEN(A1:A10)>0.
... ... or if there can be empty cells involved =SUM(IF(LEN(A1:A10)>0,1/COUNTIF(A1:A10,A1:A10))) entered with ctrl + shift & enter Click to expand... ... Picky: array-entry unnecessary. =SUMPRODUCT((A1:A10<>"")/(COUNTIF(A1:A10,A1:A10)+(A1:A10=""))) Less picky: A1:A10<>"" is likely more efficient than LEN(A1:A10)>0.
