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.

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!

×