Let's say you want to find out how many unique text or number values there are in a range. For example, if a column has the text values Bradley, Doyle, Doyle, Doyle, there are two unique values—Bradley and Doyle. If the range has the numbers 5, 6, 7, 6, the unique values are 5, 6 and 7.
To count unique values only, use a combination of the following functions, as shown in the example worksheet below:

Use the IF function to assign a value of 1 to each condition that’s TRUE.

Use the SUM function to add all unique values.

Use the FREQUENCY function to count the number of unique values. This function ignores text and zero values. For the first occurrence of a specific value, it returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a value of 0.

Use the MATCH function to return the position of a text value in a range. The FREQUENCY function uses the resulting value to evaluate the corresponding text values.

Use the LEN function to find blank cells. Blank cells have a length (character count) of 0.
Example
Copy the table to cell A1 in a blank worksheet in Excel to work with these examples of formulas that use functions.


Data 
Data 
986 
Bradley 
Doyle 
563 
67 
789 
235 

Bradley 
Doyle 
689 
789 
Doyle 
143 
56 
237 
67 
235 
Formula 
Description (Result) 
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) 
Counts the number of unique number values in cells A2:A10, but does not count blank cells or text values (4) 
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)) 
Counts the number of unique text and number values in cells B2:B10 (which must not contain blank cells) (7) 
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1)) 
Counts the number of unique text and number values in cells A2:A10 , but does not count blank cells or text values (6) 
Notes:

The formulas in this example must be entered as array formulas. Select each cell that contains a formula, press F2, and then press Ctrl+Shift+Enter.

To see a function evaluated step by step, select the cell containing the formula, and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.