Count Unique Values
The formulas in the following examples each return a count of unique values from a range of cells. Note that the formulas are listed in order of efficiency, with the one being most efficient listed first.
In the first example, while the formula is more efficient than the others, it can only deal with numerical values. Any text values within the range are ignored. However, it can accept a two or three dimensional range in addition to a one dimensional range.
In the second example, the formula can deal with numerical and/or text values. However, it requires a one dimensional range. It cannot accept a two or three dimensional range.
Lastly, in the third example, the formula can also deal with numerical and/or text values. And, it can accept a two dimensional range, but not one that's three dimensional.
With Data Containing Numerical Values
The following formula returns a count of unique numerical values in A2:A10...
=SUMPRODUCT(--(FREQUENCY(A2:A10,A2:A10)>0))
Based on the sample data, the formula returns 3.
Sample Workbook: Download
With Data Containing Numerical and/or Text Values
The following formula returns a count of unique values in A2:A10...
=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 4.
Sample Workbook: Download
Another Example With Data Containing Numerical and/or Text Values
The following formula returns a count of unique values in A2:A10...
=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10,A2:A10)))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 5.
Sample Workbook: Download