=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

153

14

4

-5.4

-5

40

40

40

40

40

38

45

49

51

43

41

52

39

36

<tbody>
</tbody>

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;">

153

14

4

-5.4

-5

40

40

40

40

40

38

45

49

51

43

41

52

39

36

<tbody>
</tbody>

</body>

153

14

4

-5.4

-5

40

40

40

40

40

38

45

49

51

43

41

52

39

36

<tbody>
</tbody>

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

153

14

4

-5.4

-5

40

40

40

40

40

38

45

49

51

43

41

52

39

36

<tbody>
</tbody>

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.

153

14

4

-5.4

-5

40

40

40

40

40

38

45

49

51

43

41

52

39

36

<tbody>
</tbody>

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.