# SMALL function excluding specific cell

#### plotting

##### New Member
https://1drv.ms/x/s!ArHhL08110anmBjx0Xyj3khzBu0S

Here is a link to the data set, sheets1:5 have random numbers in A1:N10

On sheet6 it uses =SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1)) to =SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:20)) to get the 20 smallest numbers in the range, wondering anyone has a formula, array perhaps, to ignore sheet5 cell F8 for example.

#### oldbrewer

##### Board Regular
add 10^50 to sheet5!F8 ? or make it text...

#### plotting

##### New Member
add 10^50 to sheet5!F8 ? or make it text...
The cell is used in a formula elsewhere so neither of those would work

#### oldbrewer

##### Board Regular
 4 5 6 2 3 4 9 8 7 45 in this example range1 is all the cells except the 3 in B2 the 45 is obtained by =sum(range1)

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

#### plotting

##### New Member
 4 5 6 2 3 4 9 8 7 45 in this example range1 is all the cells except the 3 in B2 the 45 is obtained by =sum(range1)

<tbody>
</tbody>
How do you make a range in name manager without a specific cell in it short of doing ='Sheet1'\$A\$1!,'Sheet1'\$A\$2!, ect. I'm guessing that's what you're referring to

#### plotting

##### New Member
 4 5 6 2 3 4 9 8 7 45 in this example range1 is all the cells except the 3 in B2 the 45 is obtained by =sum(range1)

<tbody>
</tbody>
I think I tried named ranges and it doesn't like ='Sheet1:Sheet4'!\$A\$1:\$N\$10,'Sheet5'!\$A\$1:\$E\$10,'Sheet5'!\$F\$1:\$F\$7,'Sheet5'!\$F\$9:\$F\$10,'Sheet5'!\$G\$1:\$N\$10 as a formula

#### kvsrinivasamurthy

##### Well-known Member
Use column A as helper column. Can be hidden.
In A2 then drag down up to 50 rows.

=IF(SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1))<>Sheet5!\$C\$8,SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1)),"")

Then in B2 formula then drag down till required. Column B gives result.

=IFERROR(SMALL(\$A\$2:\$A\$32,ROW(1:1)),"")

Last edited:

#### plotting

##### New Member
Use column A as helper column. Can be hidden.
In A2 then drag down up to 50 rows.

=IF(SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1))<>Sheet5!\$C\$8,SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1)),"")

Then in B2 formula then drag down till required. Column B gives result.

=IFERROR(SMALL(\$A\$2:\$A\$32,ROW(1:1)),"")
That works great, I will use that for now unless I can find a cleaner way without using a helper column but more than accomplishes the task, thanks!

#### Peter_SSs

##### MrExcel MVP, Moderator
Try (no need for helper column)
=SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1)+(Sheet5!\$F\$8<=SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1))))

Last edited:

#### kvsrinivasamurthy

##### Well-known Member
If all sheets have similar name this ARRAY formula works.
In A2 then drag down.

<sheet5!\$f\$8,0,sum(countif(indirect("sheet"&row(\$1:\$5)&"!a1:n10"),sheet5!\$f\$8))))[ code]
Code:
``=SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1)+IF(SMALL(Sheet1:Sheet5!\$A\$1:\$N\$10,ROWS(\$1:1))< Sheet5!\$F\$8,0,SUM(COUNTIF(INDIRECT("Sheet"&ROW(\$1:\$5)&"!A1:N10"),Sheet5!\$F\$8))))``
<sheet5!\$f\$8,0,rows(\$1:1)+sum(countif(indirect("sheet"&row(\$1:\$5)&"!a1:n10"),sheet5!c8))))[ code]
How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.</sheet5!\$f\$8,0,rows(\$1:1)+sum(countif(indirect("sheet"&row(\$1:\$5)&"!a1:n10"),sheet5!c8))))[></sheet5!\$f\$8,0,sum(countif(indirect("sheet"&row(\$1:\$5)&"!a1:n10"),sheet5!\$f\$8))))[>

Last edited:

1,082,152
Messages
5,363,453
Members
400,737
Latest member
vipamuk

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...