Forum Discussion

Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Nov 02, 2018
Solved

Count unique values in a endless column

Hi All,   I'm stuck :) I like to count the amount of unique values in a column. So far no problem with the following formula:   ={SUMPRODUCT(1/COUNTIF(A2:A2733,A2:A2733))}   This gives me the ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 02, 2018

    Yes, COUNTIF could be quite slow on big range. FREQUENCY shall be faster

    =SUM(IF(FREQUENCY(IF(A2:A35000<>"",MATCH(A2:A35000,A2:A35000,0)),ROW(A2:A35000)-ROW($A$2)+1),1))

    Above is array formula (Ctrl+Shift+Enter)

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 02, 2018

Hi Dennis,

 

Better to use tables, with ranges formula could be

=SUMPRODUCT((A2:A2733<>"")/COUNTIF(A2:A2733,A2:A2733&""))

 

Resources