SMALL function with duplicates
The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers.
The following formula in cell E3 extracts the smallest number from B3:B9.
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 to cells below it will extract 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 example below.
Formula in cell E3:
Array formula in cell E4:
The formula above is 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.
Download Excel *.xlsx file
SMALL function with duplicates.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
How to use the MINIFS function
The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The […]
Find the smallest value in a list that is larger than a number
Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel […]
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]
The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove […]
How to ignore zeros using the SMALL function
The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros. Weekly Blog EMAIL […]
4 Responses to “SMALL function with 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?