Count unique values among duplicates

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.