Excel - Getting the number of distinct values in a column

April 2018



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.
  • 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.
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 (https://ccm.net/).
Opening Excel files on Server- very slow
Excel - The COLUMN function