Author: Oscar Cronquist Article last updated on November 30, 2018

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, array 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,))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell E2

Step 1 - Count frequency of each value

The COUNTIF function counts values based on a condition or criteria, in this case, it counts all values in cell range.

COUNTIF($B$3:$B$21,$B$3:$B$21)

becomes

COUNTIF({2; 12; 9; 10; 0; 5; 18; 18; 12; 5; 12; 19; 3; 1; 8; 16; 10; 4; 17},{2; 12; 9; 10; 0; 5; 18; 18; 12; 5; 12; 19; 3; 1; 8; 16; 10; 4; 17})

and returns

{1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}.

Step 2 - Extract duplicate numbers

The IF function returns the row number if cell is a duplicate. FALSE returns "" (nothing).

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

becomes

IF({1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}>,$B$3:$B$21,)

becomes

IF({1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}>,{2; 12; 9; 10; 0; 5; 18; 18; 12; 5; 12; 19; 3; 1; 8; 16; 10; 4; 17},)

and returns

{0; 12; 0; 10; 0; 5; 18; 18; 12; 5; 12; 0; 0; 0; 0; 0; 10; 0; 0}.

Step 3 - Return largest value

The MAX function returns the maximum value from a cell range or array.

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

becomes