# SMALL function ignore duplicates

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers.

#### Table of Contents

## 1. SMALL function with duplicates (Excel 2016)

The formulas in column E, shown in the picture above, extract the k-th smallest value from B3:B9 ignoring the duplicate numbers.

The following formula in cell E3 extracts the smallest number:

However, we need to use another formula in the cells below to ignore duplicate values. The formula in cell E4 extracts the second smallest number from B3:B9.

When you copy this formula and paste it to cells below it will extract the third, fourth, and so on, smallest value ignoring duplicate values.

The MINIFS function returns the smallest value depending on the condition, in this case, it looks for values larger than the previous value in the cell above, meaning it will ignore duplicate numbers.

I can't use this formula in cell E3 because there is no formula above it.

The MINIFS function was introduced in Excel 2016, if you have an earlier version of Excel see section 2 below.

### 1.1 Explaining formula

#### Step 1 - MINIFS function

The MINIFS function calculates the smallest value based on a given set of criteria.

MINIFS(*min_range*, *criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*], ...)

min_range |
Required. A cell reference pointing to the numbers. |

criteria_range1 |
Required. Cells to evaluate based on the criteria. |

criteria1 |
Required. Criteria in the form of a number, expression, or text. |

[criteria_range2] |
Optional. Up to 126 additional arguments. |

[criteria2] |
Optional. Up to 126 additional arguments. |

#### Step 2 - Populate arguments

*min_range - $B$3:$B$9*

*criteria_range1 - $B$3:$B$9*

*criteria1 - ">"&E3*

MINIFS($B$3:$B$9, $B$3:$B$9, ">"&E3)

#### Step 3 - Evaluate formula

MINIFS($B$3:$B$9, $B$3:$B$9, ">"&E3)

becomes

MINIFS({40; 10; 40; 10; 50; 30; 30},{40; 10; 40; 10; 50; 30; 30},">"&10)

and returns 30 in cell E4.

Note, it won't return duplicate numbers when you copy cell E4 and paste it to the cells below.

## 2. SMALL function with duplicates (previous Excel versions)

Formula in cell E3:

Array formula in cell E4:

The formula above is an array formula.

### 2.1 How to enter an array formula

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 enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining array formula

#### Step 1 - Check which numbers are larger than cell E3

The less than sign is a logical operator, it allows you to compare numbers and text strings. The result is either TRUE or FALSE, they are boolean values and will be used in step 2 in an IF functions logical test argument.

E3<$B$3:$B$9

becomes

10<{40; 10; 40; 10; 50; 30; 30}

and returns {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE}.

#### Step 2 - Filter numbers based on logical test

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(E3<$B$3:$B$9, $B$3:$B$9, "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE}, $B$3:$B$9, "")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE}, {40; 10; 40; 10; 50; 30; 30}, "")

and returns {40; ""; 40; ""; 50; 30; 30}

Numbers smaller than or equal to the condition are filtered out from the array.

#### Step 3 - Extract the smallest number from the array

The MIN function returns the smallest number from a cell range or array ignoring text and blank values.

MIN(IF(E3<$B$3:$B$9, $B$3:$B$9, ""))

becomes

MIN({40; ""; 40; ""; 50; 30; 30})

and returns 30 in cell E3.

## 3. SMALL function with duplicates (Excel 365)

Formula in cell E3:

### 3.1 Explaining formula

#### Step 1 - Extract unique distinct values

The UNIQUE function returns unique distinct numbers.

UNIQUE(B3:B9)

becomes

UNIQUE({40; 10; 40; 10; 50; 30; 30})

and returns {40; 10; 50; 30}.

#### Step 2 - Sort values from small to large

The SMALL function returns the k-th smallest number.

SMALL(array, k)

SMALL(UNIQUE(B3:B9),ROWS($A$1:A1))

becomes

SMALL({40; 10; 50; 30},ROWS($A$1:A1))

The ROWS function returns the number of rows based on the specified cell reference.

ROWS(ref)

The reference $A$1:A1 contains an absolute part and a relative part, the absolute part stays the same, however, the relative part changes when you copy the cell and paste it to cells below.

SMALL({40; 10; 50; 30},ROWS($A$1:A1))

becomes

SMALL({40; 10; 50; 30},1)

and returns 10 in cell E3.

## 4. SMALL function ignore duplicates based on a condition

The image above demonstrates two formulas that let you extract the smallest number based on a condition ignoring duplicate numbers.

The formula in cell F5 extracts the smallest number based on a condition specified in cell F2, however, this formula works only in the first cell. Cell F6 and cells below require a different formula.

The array formulas below work in all Excel versions, here is how to enter an array formula.

Array formula in cell F5:

Array formula in cell F6:

Copy cell F6 and paste it to cells below as far as needed.

### 4.1 Explaining formula in cell F5

#### Step 1 - Logical test

The equal sign is a logical operator and the result is a boolean value, TRUE or FALSE.

$F$2=$B$3:$B$9

becomes

"A"={"A"; "A"; "A"; "A"; "B"; "A"; "A"}

and returns {TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}.

#### Step 2 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF($F$2=$B$3:$B$9, $C$3:$C$9, "")

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}, $C$3:$C$9, "")

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}, {40; 10; 40; 10; 50; 30; 60}, "")

and returns {40; 10; 40; 10; ""; 30; 60}.

#### Step 3 - Return smallest number

The MIN function returns the smallest number from a cell range or array ignoring text and blank values.

MIN(cell_ref)

MIN(IF($F$2=$B$3:$B$9, $C$3:$C$9, ""))

becomes

MIN({40; 10; 40; 10; ""; 30; 60})

and returns 10.

### 4.2 Explaining formula in cell F6

#### Step 1 - Identify values equal to condition

The equal sign is a logical operator and the result is a boolean value, TRUE or FALSE.

$F$2=$B$3:$B$9

becomes

"A"={"A"; "A"; "A"; "A"; "B"; "A"; "A"}

and returns {TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}.

#### Step 2 - Check if numbers are smaller than previous number above

The less than sign is a logical operator, it allows you to compare numbers and text strings. The result is either TRUE or FALSE, they are boolean values.

F5<$C$3:$C$9

becomes

10<{40; 10; 40; 10; 50; 30; 60}

and returns

{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE}

#### Step 3 - Multiply arrays

Both test must be true and to do that we need to multiply the array, in other words, apply AND logic.

Use the asterisk to multiply values or arrays.

* (asterisk) - Both logical expressions must match (AND logic)

The AND logic behind this is that

- TRUE * TRUE = TRUE (1)
- TRUE * FALSE = FALSE (0)
- FALSE * FALSE = FALSE (0)

($F$2=$B$3:$B$9)*(F5<$C$3:$C$9)

The parentheses control the order of operation.

($F$2=$B$3:$B$9)*(F5<$C$3:$C$9)

becomes

{TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}*{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE}

and returns {1; 0; 1; 0; 0; 1; 1}.

#### Step 4 - Replace 1 with corresponding number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(($F$2=$B$3:$B$9)*(F5<$C$3:$C$9),$C$3:$C$9,"")

becomes

IF({1; 0; 1; 0; 0; 1; 1},$C$3:$C$9,"")

becomes

IF({1; 0; 1; 0; 0; 1; 1},{40; 10; 40; 10; 50; 30; 60},"")

and returns {40; ""; 40; ""; ""; 30; 60}.

#### Step 5 - Extract the smallest number from the array

The MIN function returns the smallest number from a cell range or array ignoring text and blank values.

MIN(cell_ref)

MIN(IF(($F$2=$B$3:$B$9)*(F5<$C$3:$C$9),$C$3:$C$9,"")))

becomes

MIN({40; ""; 40; ""; ""; 30; 60})

and returns 30.

### Get Excel *.xlsx file

### 4 Responses to “SMALL function ignore duplicates”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

The following array formula works fine if you don't want two different formulas in one column:

=SMALL(IF(COUNTIF($E$2:E2,$B$3:$B$9)=0,$B$3:$B$9,""),1)

The following array formula in E3 and down is yet another option:

MIN(IF(N(E2)<$B$3:$B$9, $B$3:$B$9, ""))

Leonid,

thanks for commenting.

Your formula works fine if all numbers are larger than 0 (zero).

Thank you for this, I have an array with multiple zeros, I want the formula to return each cell reference containing zero, please how can I achieve this?