Easiest Way to Count Unique values in Excel
The easiest and newest way to count unique values in Excel is to use the unique function, which is a new dynamic array function. Then, once you can use the count function, you get an instant count of the unique values.
=COUNTA(UNIQUE(H5:H14, FALSE, TRUE))
This is a new function which only available in Excel 365
The formula counts how many values appear only once. It pulls out only the values that show up a single time and throws away anything that repeats.
The UNIQUE, with that final FALSE, returns one copy of each value, keeping a single instance of repeated items instead of dropping them. COUNTA then counts that list.
Count Unique Values in Any Excel Version
If you don’t have the UNIQUE function in your Excel version, you can use a specific formula with the SUMPRODUCT and COUNTIF functions. This formula is also smart and powerful enough to return the count of unique values.
=SUMPRODUCT(1/COUNTIF(H5:H14, H5:H14))