Excel provides a variety of ways to count the number of unique values in a set of data. For example, you can use a filter to find unique values, or you can use function to find unique values.
Count unique numerical values
To count only unique numerical values in a set of data, you can use this set of functions: SUM, IF, and FREQUENCY. The FREQUENCY function is used to find each unique numerical value because it ignores text and zero values, and the IF function is used to return a value of 1 for each unique numerical value. The SUM function is used to add all of the instances of 1 that are returned by the IF function, which results in a count of all unique numerical values in the data set.

Select a blank cell where you want to display the number of unique values. This is where the formula will be entered.

On the Formulas tab, under Function, click Formula Builder.

In the Formula Builder list, doubleclick SUM.
The formula begins building in the formula bar.

In the Formula Builder list, doubleclick IF.

In the Formula Builder list, doubleclick FREQUENCY.
The formula bar now shows SUM(IF(FREQUENCY())).

Under Arguments, click the box next to data_array, and then type the range of cells where you want to count unique values.

Under Arguments, click the box next to bins_array, and again type the range of cells where you want to count unique values. The range entered in Step 7 must be the same as the range entered in Step 6.

Under Arguments, click the arrow next to FREQUENCY.
The Formula Builder displays the IF function arguments.

Under Arguments, click the word True next to is, and then on the popup menu, click > (Greater Than).

Under Arguments, click the box next to value2 and type 0.

Under Arguments, click the box next to then and type 1.

Press RETURN .
The total number of unique values, excluding blank cells and text values, appears in the cell where you entered the formula.
Note: If there are blank cells in the selected range, you will see an error indicator. You can ignore the error indicator about formulas that refer to empty cells.
Count unique text and numerical values, excluding blank cells
To count only unique text values in a set of data, you can use nested functions in an array formula: The LEN function identifies blank cells because they return a length of 0. The MATCH function returns the position of text values in a set of data, which is a unique number. The position of text values returned by MATCH are evaluated by the FREQUENCY function, which identifies the unique text and numerical values. The IF function returns a value of 1 for each unique value. The SUM function adds all of the 1s that are returned by the IF function, which results in a count of all unique text and numerical values in the data set, excluding blank cells.

Select a blank cell where you want to display the number of unique values. This is where the formula will be entered.

Type =SUM(IF(FREQUENCY(IF(LEN( range)>0,MATCH( range, range,0),""), IF(LEN( range)>0,MATCH( range, range,0),""))>0,1)) , where range is the range of cells where you want to count unique text and numerical values, such as A1:A50.

Press + RETURN to enter the formula as an array formula.
The total number of unique text and numerical values, excluding blank cells, appears in the cell where you entered the formula.
Note: If you do not press +Shift+Enter, the formula is not entered as an array formula, and the error #VALUE! is returned. In Excel 2016 for Mac you can use Ctrl+Shift+Enter, just like in Windows versions of Excel.
View an example
You have been tracking inventory in the sheet below. Use the formulas in the following table to summarize your inventory transactions.
Transaction 
Date 
Location 
Item 
Quantity 
201000 
5/1 
California 
tent 
2 
201001 
5/4 
Washington 
headlamp 
2 
201002 
5/5 
Washington 
sleeping bag 
1 
201003 
5/5 
Washington 
headlamp 
1 
201004 
5/6 
California 
tent 
3 
201005 
5/13 
Oregon 
backpack 
1 
201006 
5/16 
Oregon 
backpack 
1 
201007 
5/19 
California 
car rack 
2 
201008 
5/21 
California 
backpack 
1 
201009 
5/28 
California 
car rack 
1 
Formula 
Description (Result) 

=SUM(IF(FREQUENCY(IF(LEN(D2:D11)>0,MATCH(D2:D11,D2:D11,0),""), IF(LEN(D2:D11)>0,MATCH(D2:D11,D2:D11,0),""))>0,1)) 
The number of unique items tracked in the warehouse (5). 

=SUM(IF(FREQUENCY(IF(LEN(C2:C11)>0,MATCH(C2:C11,C2:C11,0),""), IF(LEN(C2:C11)>0,MATCH(C2:C11,C2:C11,0),""))>0,1)) 
The number of unique warehouse locations (3). 
Note: Each formula in the example must be entered as an array formula. Type the formula and then press +Shift+Enter, or Ctrl+Shift+Enter for Excel 2016 for Mac. If the formula is not entered as an array formula, the result will be incorrect.
See also
Filter for or remove duplicate values
Count numbers greater than or less than a number