Count-duplicate-and-distinct-duplicate-values-in-a-column-in-excel

Count duplicate distinct values

Formula in E18:=ABS(SUM(IF(COUNTIF(A2:A16, A2:A16)>1, (1/COUNTIF(A2:A16, A2:A16))-1, 0))) + CTRL + SHIFT + ENTER

Download excel example file.
Count duplicate distinct values in a column in excel.xls
(Excel 97-2003 Workbook *.xls)

Functions:

ABS(number)
Returns the absolute value of a number, a number without its sign.

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SUM(number1,[number2],)
Adds all the numbers in a range of cells

This blog article is one out of twelve articles on the same subject "count unique and unique distinct".

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct values in a column in excel
  2. Count unique values and unique distinct values in two ranges combined
  3. Count unique values and unique distinct values in three ranges combined in excel
  4. Count unique and unique distinct values in a multicolumn range in excel
  5. Count unique distinct values in two columns in excel
  6. Count unique distinct values in three columns combined in excel
  7. Count unique distinct values in two columns with date criteria in excel
  8. Count unique distinct months in excel
  9. Count unique distinct records in a date range and a numeric range in excel
  10. Count unique distinct values using date criteria in a range in excel