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.
|
|
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.
OSum!