Show All

Hide All

Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains:

• The values 5, 6, 7, and 6, then the result is three unique values—5 , 6 and 7.
• The values "Buchanan", "Dodsworth", "Dodsworth", "Dodsworth", then the result is two unique values—"Buchanan" and "Dodsworth".

There are several ways to count unique values among duplicates.

• Count the number of unique values by using a filter

• Count the number of unique values by using functions

Count the number of unique values by using a filter

You can use the Advanced Filter dialog box to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.

1. Select the range of cells, or make sure the active cell is in a table.

   Make sure the range of cells has a column heading.

2. On the Data tab, in the Sort & Filter group, click Advanced.

   The Advanced Filter dialog box is displayed.

3. Click Copy to another location.
4. In the Copy to box, enter a cell reference.

   Alternatively, click Collapse Dialog to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog .

5. Select the Unique records only check box, and click OK.

   The unique values from the selected range are copied to the new location beginning with the cell you specified in the Copy to box.

6. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument, excluding the column heading. For example, if the range of unique values is B2:B45, then enter:

   =ROWS(B2:B45)

 Top of Page

Count the number of unique values by using functions

Use the IF, SUM, FREQUENCY, MATCH, and LEN functions to do this task:

• Assign a value of 1 to each true condition by using the IF function.
• Add the total by using the SUM function.
• Count the number of unique values by using the FREQUENCY function. The FREQUENCY function ignores text and zero values. For the first occurrence of a specific value, this function 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 zero.
• Return the position of a text value in a range by using the MATCH function. This value returned is then used as an argument to the FREQUENCY function so that the corresponding text values can be evaluated.
• Find blank cells by using the LEN function. Blank cells have a length of 0.

Example

The example may be easier to understand if you copy it to a blank worksheet.

 Notes 

• The formulas in this example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). 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.

Function details

FREQUENCY

MATCH

LEN, LENB

SUM

IF

 Top of Page