SMALL function excluding specific cell

P

plotting

New Member
Joined
Jun 13, 2016
Messages
21
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.

Thanks in advance for looking!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]in this example range1 is all the cells except the 3 in B2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the 45 is obtained by =sum(range1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]in this example range1 is all the cells except the 3 in B2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the 45 is obtained by =sum(range1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
oldbrewer said:
[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]in this example range1 is all the cells except the 3 in B2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the 45 is obtained by =sum(range1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
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:
Upvote 0
kvsrinivasamurthy said:
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)),"")
Click to expand...

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!
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
92
Pingaware
P
A
  • Question Question
VBA Highlight cell if contains specific text
Replies
4
Views
2K
DanteAmor
DanteAmor
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
286
cokeoverpepsi123
C
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
199
Pbobas
P
B
  • Solved
use a Named Range LastRow value instead of a hard coded Column and Row number question
Replies
2
Views
422
blbat
B

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top