# 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.

## How to choose the formula? ## 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
911
#N/A 911
Excel 444
888 555
Formula Description (Result)
=SUM(1/COUNTIF(B2:B9,B2:B9)) Counts all unique values (include number values, text values and error values) in cells B2:B9 (which must not contain blank cells) (6)
=SUM(IF(B2:B9="","",1/COUNTIF(B2:B9,B2:B9))) Counts the number of unique text and number values in cells B2:B9 (which must not contain error value cells), but does not count blank cells (empty cells) (6)
=SUM(IF(ISBLANK(A2:A9),"",1/COUNTIF(A2:A9,A2:A9))) Counts the number of unique text, number and error values in cells A2:A9, but does not count blank cells (empty cells) (5)
=SUM(IF(ISNUMBER(A2:A9),1/COUNTIF(A2:A9,A2:A9),"")) Counts the number of unique number values in cells A2:A9 (2)
=SUM(IF(ISTEXT(A2:A9),1/COUNTIF(A2:A9,A2:A9),"")) Counts the number of unique text values in cells A2:A9 (2)

Note: The formula in the example must be entered as an array formula. After copying the formula to a blank cell, select the cell, press F2, and then press CTRL+SHIFT+ENTER.

1 comment
1. sandeep

OSum!