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

• Count unique values in a column in excel
• Count unique distinct values in two columns in excel
• Count unique distinct values in three columns combined in excel
• Count unique and unique distinct values in a multicolumn range in excel
  
• Count unique values and unique distinct values in two ranges combined
  
• Count unique values and unique distinct values in three ranges combined in excel
• How to count unique combined column values
• How to count unique distinct records in a date range
  
• Count unique distinct records in a date range and a numeric range in excel
  
• Count unique distinct values in two columns with date criteria in excel
  
• Count unique distinct months in excel
• Count duplicate distinct values in a column in excel

Related posts:

1. Count unique distinct months in excel
2. Count unique distinct records in a date range and a numeric range in excel
3. Count unique distinct values using date criteria in a range in excel
4. Count unique records by date in excel
5. Count unique distinct text values in a range in excel
6. Unique distinct list sorted based on occurrance in a column in excel
7. Count unique values in two lists combined in excel
8. Unique distinct list from a column sorted A to Z using array formula in excel