Question: How do I get the largest and smallest unique and duplicate value?

The image below shows you a list of numbers in column B ($B$3:$B$21).

Max duplicate value, formula in E2:

The largest number in the list above is 19 but it is a unique number meaning it exists only once. Number 18 is the largest duplicate number, 18 is in cell B9 and B10.

=MAX(IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, $B$3:$B$21, ))

Recommended article

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Comments(41) Filed in category: Duplicate values, Excel

Min duplicate value, formula in E4:

The smallest number in the list above is 0 but it is a unique number meaning it exists only once. Number 5 is the smallest duplicate number, 18 is in cell B8 and B12.

=MIN(IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, $B$3:$B$21, ""))

Recommended article

Extract a list of alphabetically sorted duplicates based on a condition

The following image shows you a data set in column B and C. The formula in cell E2 extracts a […]

Comments(1) Filed in category: Duplicate values, Excel

Max unique value, formula in E6:

The largest unique number in the list above is 19.

=MAX(IF(COUNTIF($B$3:$B$21, $B$3:$B$21)=1, $B$3:$B$21, ))

Recommended article

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Min unique value, formula in E8:

The smallest unique number in the list above is 0.

=MIN(IF(COUNTIF($B$3:$B$21, $B$3:$B$21)=1, $B$3:$B$21, ""))

Recommended article

Extract unique values from a range using array formula in excel

Question: How do I extract values only occuring once in a range? Answer: A range (tbl_text) containing text values Array […]

Comments(2) Filed in category: Excel, Unique values

Download excel *.xlsx file

Min and max unique and duplicate values.xlsx

Functions in this article:

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

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

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.