Excel - Getting the number of distinct values in a column

January 2017



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


Published by aakai1056. Latest update on February 15, 2013 at 05:14 PM by aquarelle.
This document, titled "Excel - Getting the number of distinct values in a column," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).