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
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, "")
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)
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},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
returns {TRUE; 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, "")
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
returns {TRUE; 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
returns {TRUE; FALSE; ... ; 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.
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,"")
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
Small category
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]
Excel categories
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.
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?