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.

    Formulas tab, Function group

  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 Up 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 COMMAND + 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 COMMAND +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 COMMAND +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

Count how often a value occurs

Ways to count cells in a range of data

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×