Count Unique Values
The following formulas count the number of unique values in a range of cells. The formula in the first example is able to deal with data that contains numerical and/or text values. However, it requires a one dimensional range. The formula in the second example is also able to deal with data that contains numerical and/or text values. And, in addition, it accepts a two dimensional range. But, it's not as efficient as the first one. Lastly, the formula in the third example can only deal with numerical values. Any text values within the range are ignored. However, this one is more efficient than the first two formulas.
With Data Containing Numerical and/or Text Values in a One Dimensional Range
The following formula counts the number 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
With Data Containing Numerical and/or Text Values in a Two Dimensional Range
The following formula counts the number of unique values in A2:B10...
=SUM(IF(A2:B10<>"",1/COUNTIF(A2:B10,A2:B10)))
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
With Data Containing Numerical Values
The following formula counts the number of unique numerical values in A2:A10...
=SUMPRODUCT(--(FREQUENCY(A2:A10,A2:A10)>0))
Based on the sample data, the formulas return 3.
Sample Workbook: Download