=small function - skipping cells with formulae but no value

[goliath1]

Hi i searched thru the forum but could not find any reference to my specific question.

I want the 5 lowest values in a row. The cells in the row may or may not have values. When i use the =SMALL function it thinks my blank cell is one of 5 smallest.
=SUM(SMALL(G4:U4,{1,2,3,4,5})) is the formula in the cell below with value=153. It is summing the 5 lowest values,

It is summing the 5 cells with the "X" above them. How do i skip the blank cell in YELLOW? BTW, each of these colored cells contain a vlookup formula.
G4---------------------------------------------------------------------------------U4
x x x x x[TABLE="width: 781"]
<tbody>[TR]
[TD="class: xl69, width: 50"]153[/TD]
[TD="class: xl69, width: 50"]14[/TD]
[TD="class: xl70, width: 50"]4[/TD]
[TD="class: xl71, width: 43"]-5.4[/TD]
[TD="class: xl68, width: 57"]-5[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 33, align: center"] [/TD]
[TD="class: xl70, width: 33, align: center"]38[/TD]
[TD="class: xl70, width: 33, align: center"]45[/TD]
[TD="class: xl70, width: 33, align: center"]49[/TD]
[TD="class: xl70, width: 33, align: center"]51[/TD]
[TD="class: xl70, width: 33, align: center"]43[/TD]
[TD="class: xl70, width: 33, align: center"]41[/TD]
[TD="class: xl70, width: 33, align: center"]52[/TD]
[TD="class: xl70, width: 33, align: center"]39[/TD]
[TD="class: xl70, width: 39, align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]

I want it to pick up these 5 values, 40, 40, 38, 39, 36
x x x x x
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 781"]
<tbody>[TR]
[TD="class: xl69, width: 50"]153[/TD]
[TD="class: xl69, width: 50"]14[/TD]
[TD="class: xl70, width: 50"]4[/TD]
[TD="class: xl71, width: 43"]-5.4[/TD]
[TD="class: xl68, width: 57"]-5[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 33"] [/TD]
[TD="class: xl70, width: 33"]38[/TD]
[TD="class: xl70, width: 33"]45[/TD]
[TD="class: xl70, width: 33"]49[/TD]
[TD="class: xl70, width: 33"]51[/TD]
[TD="class: xl70, width: 33"]43[/TD]
[TD="class: xl70, width: 33"]41[/TD]
[TD="class: xl70, width: 33"]52[/TD]
[TD="class: xl70, width: 33"]39[/TD]
[TD="class: xl70, width: 39"]36

[/TD]
[/TR]
</tbody>[/TABLE]
</body>[TABLE="width: 588"]
<tbody>[TR]
[TD="class: xl69, width: 50"]153[/TD]
[TD="class: xl69, width: 50"]14[/TD]
[TD="class: xl70, width: 50"]4[/TD]
[TD="class: xl71, width: 43"]-5.4[/TD]
[TD="class: xl68, width: 57"]-5[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 33, align: center"] [/TD]
[TD="class: xl70, width: 33, align: center"]38[/TD]
[TD="class: xl70, width: 33, align: center"]45[/TD]
[TD="class: xl70, width: 33, align: center"]49[/TD]
[TD="class: xl70, width: 33, align: center"]51[/TD]
[TD="class: xl70, width: 33, align: center"]43[/TD]
[TD="class: xl70, width: 33, align: center"]41[/TD]
[TD="class: xl70, width: 33, align: center"]52[/TD]
[TD="class: xl70, width: 33, align: center"]39[/TD]
[TD="class: xl70, width: 39, align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]

any ideas?

 

[goliath1]

ok...the formatting went haywire...sorry.

 

[goliath1]

Hi i searched thru the forum but could not find any reference to my specific question.

I want the 5 lowest values in a row. The cells in the row may or may not have values. When i use the =SMALL function it thinks my blank cell is one of 5 smallest.
=SUM(SMALL(G4:U4,{1,2,3,4,5})) is the formula in the cell below with value=153. It is summing the 5 lowest values,

It is summing the 5 cells with the "X" above them. How do i skip the blank cell? BTW, each of these cells G4:U4 contain a vlookup formula.
-----------------------------------G4----------------------------------------------------------------------------------U4
--------------------------------------------------------------X---X---X------------------------------------------X-----X

[TABLE="class: cms_table, width: 781"]
<tbody>[TR]
[TD="class: cms_table_xl69, width: 50"]153[/TD]
[TD="class: cms_table_xl69, width: 50"]14[/TD]
[TD="class: cms_table_xl70, width: 50"]4[/TD]
[TD="class: cms_table_xl71, width: 43"]-5.4[/TD]
[TD="class: cms_table_xl68, width: 57"]-5[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"][/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]38[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]45[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]49[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]51[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]43[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]41[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]52[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]39[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]


I want it to pick up these 5 values, 40, 40, 38, 39, 36 but it appears to be selecting 40,38,39,36 and blank.

[TABLE="class: cms_table, width: 781"]
<tbody>[TR]
[TD="class: cms_table_xl69, width: 50"]153[/TD]
[TD="class: cms_table_xl69, width: 50"]14[/TD]
[TD="class: cms_table_xl70, width: 50"]4[/TD]
[TD="class: cms_table_xl71, width: 43"]-5.4[/TD]
[TD="class: cms_table_xl68, width: 57"]-5[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 33"][/TD]
[TD="class: cms_table_xl70, width: 33"]38[/TD]
[TD="class: cms_table_xl70, width: 33"]45[/TD]
[TD="class: cms_table_xl70, width: 33"]49[/TD]
[TD="class: cms_table_xl70, width: 33"]51[/TD]
[TD="class: cms_table_xl70, width: 33"]43[/TD]
[TD="class: cms_table_xl70, width: 33"]41[/TD]
[TD="class: cms_table_xl70, width: 33"]52[/TD]
[TD="class: cms_table_xl70, width: 33"]39[/TD]
[TD="class: cms_table_xl70, width: 39"]36
[/TD]
[/TR]
</tbody>[/TABLE]





any ideas?

 

[Jonmo1]

Try this formula entered as an Array

=SUM(SMALL(IF(G3:U3="X",G4:U4),{1,2,3,4,5}))

This is an array formula that requires special keystroke to enter it.
Instead of pressing ENTER after typing the formula, you must press CTRL + SHIFT + ENTER
You will know the formula is correctly entered when it is enclosed in {brackets}

 

[barry houdini]

I think SMALL will ignore blanks and text so if it's picking the "blank" cell I think that cell contains a zero. You can use this formula to sum the smallest 5 without zero

=SUM(AGGREGATE(15,6,G4:U4/(G4:U4<>0),{1,2,3,4,5}))

 

[goliath1]

THE "x" in the post was only to point out which cells i needed to pick out.

I just want the function =SMALL to ignore cells that are not numeric.

 

[Jonmo1]

THE "x" in the post was only to point out which cells i needed to pick out.

I just want the function =SMALL to ignore cells that are not numeric.

My mistake, I misread your post thinking you wanted the ones that has an x in a row above..

As Barry said, small DOES ignore blanks and Non Numeric Values.
I would guess that your blanks are actually real 0's. But you have 0s hidden either by some formatting, or in Advanced options you can uncheck "Show a zero in cells with zero value"

 

[barry houdini]

As per my reply, SMALL function will only pick numbers, so your blank is probably really a zero (formatted to display as a blank?) so try the formula I suggested in my last post......

 

[goliath1]

I think SMALL will ignore blanks and text so if it's picking the "blank" cell I think that cell contains a zero. You can use this formula to sum the smallest 5 without zero

=SUM(AGGREGATE(15,6,G4:U4/(G4:U4<>0),{1,2,3,4,5}))

WOOHOO. i have been working on this for hours. works like a top. thx again.