# Count unique values among duplicates

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.

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

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

3. In the Formula Builder list, double-click SUM.

The formula begins building in the formula bar.

4. In the Formula Builder list, double-click IF.

5. In the Formula Builder list, double-click FREQUENCY.

The formula bar now shows SUM(IF(FREQUENCY())).

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

7. 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.

8. Under Arguments, click the arrow next to FREQUENCY.

The Formula Builder displays the IF function arguments.

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

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

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

12. 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.

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

2. 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.

3. 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.