How To Count Unique Values In Excel Using Formula

Sometimes you may want to count unique values exist in a range that contains duplicate values. You can count the number of unique values by using a filter, but in this article we use Excel formula to solve it.

Count the unique values (not contain blank cells)

`=SUMPRODUCT(1/COUNTIF(range,range))`

or

`{=SUM(1/COUNTIF(range,range))}`

NOTE:

• This is an array formula enclosed in curly braces {}. Do not copy or type these yourself. After copying the formula to a blank cell, select the cell, press F2, and then press CTRL+SHIFT+ENTER.
• range: one or more cells, eg. A1:A10, B2:C5
• This formula counts number values, text values and error values, but the range must not contain blank cells (empty cells).
• Applies to Excel 2003 and later.

Count the unique values (contain blank cells)

`{=SUM(IF(ISBLANK(range),"",1/COUNTIF(range,range)))}`

NOTE:

• This formula counts number values, text values and error values, the range may contain blank cells (empty cells).
• Applies to Excel 2007, 2010, 2013.

Count the unique values (not contain error cells)

`{=SUM(IF(range="","",1/COUNTIF(range,range)))}`

NOTE:

• This formula counts number values, text values, the range may contain blank cells (empty cells), but must not contain error value cells
• Applies to Excel 2003 and later.

Count the unique text values

`{=SUM(IF(ISTEXT(range),1/COUNTIF(range,range),""))}`

NOTE:

• This formula counts unique text values.
• Applies to Excel 2007, 2010, 2013.

Count the unique number values

`{=SUM(IF(ISNUMBER(range),1/COUNTIF(range,range),""))}`

NOTE:

• This formula counts unique number values.
• Applies to Excel 2007, 2010, 2013.

Examples

The example may be easier to understand if you copy A1:B15 to a blank worksheet or download here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
A B
Data Data
Excel Microsoft
888 119