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?

How To Count Unique Values In Excel Using 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
999 Google
911
#N/A 911
Word Google
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.

Leave a Reply

Your email address will not be published. Required fields are marked *