Excel - Getting the number of distinct values in a column

December 2016



Issue


I want to know the number of distinct values ??in a column:
Example: Column A: values in cell ??1 to 9: 1 2 3 4 5 6 4 5 6: the result is 6 because there are six different values.

Solution

  • This array formula will give the number unique values ??in the range A1: A10
  • =SUM(1/COUNTIF(A1:A10,A1:A10)) 
  • Validate the array formula with Ctrl + Shift + Enter.

Related :

This document entitled « Excel - Getting the number of distinct values in a column » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.