Sum(small excluding text

[mheals]

I'm doing a masters pool and have the following line for each entry

<table border="0" cellpadding="0" cellspacing="0" width="2162"><col width="112"><col width="31"><col width="97"><col width="32"><col width="118"><col width="34"><col width="101"><col width="32"><col width="109"><col width="30"><col width="136"><col width="26"><col width="115"><col width="29"><col width="115"><col width="29"><col width="115"><col width="32"><col width="115"><col width="30"><col width="106"><col width="33"><col width="127"><col width="32"><col width="106"><col width="33"><col width="106"><col width="31"><col width="116"><col width="34"><tr height="20"> <td class="xl63" style="height: 15pt; width: 84pt;" width="112" height="20">Martin Kaymer</td> <td class="xl64" style="width: 23pt;" width="31">6</td> <td class="xl63" style="width: 73pt;" width="97">Rory Mcilroy</td> <td class="xl65" style="width: 24pt;" width="32">-7</td> <td class="xl63" style="width: 89pt;" width="118">Dustin Johnson</td> <td class="xl64" style="width: 26pt;" width="34">2</td> <td class="xl63" style="width: 76pt;" width="101">Bubba Watson</td> <td class="xl65" style="width: 24pt;" width="32">2</td> <td class="xl63" style="width: 82pt;" width="109">Hunter Mahan</td> <td class="xl65" style="width: 23pt;" width="30">3</td> <td class="xl63" style="width: 102pt;" width="136">Martin Laird</td> <td class="xl64" style="width: 20pt;" width="26">2</td> <td class="xl63" style="width: 86pt;" width="115">Yong-Eun Yang</td> <td class="xl65" style="width: 22pt;" width="29">-8</td> <td class="xl63" style="width: 86pt;" width="115">Padraig Harrington</td> <td class="xl65" style="width: 22pt;" width="29">5</td> <td class="xl63" style="width: 86pt;" width="115">K.J. Choi</td> <td class="xl65" style="width: 24pt;" width="32">-6</td> <td class="xl63" style="width: 86pt;" width="115">Mark Wilson</td> <td class="xl64" style="width: 23pt;" width="30">4</td> <td class="xl63" style="width: 80pt;" width="106">Angel Cabrera</td> <td class="xl65" style="width: 25pt;" width="33">-2</td> <td class="xl63" style="width: 95pt;" width="127">Stephen Marino</td> <td class="xl65" style="width: 24pt;" width="32">1</td> <td class="xl63" style="width: 80pt;" width="106">Jeffrey Overton</td> <td class="xl65" style="width: 25pt;" width="33">1</td> <td class="xl63" style="width: 80pt;" width="106">Stewart Cink</td> <td class="xl65" style="width: 23pt;" width="31">2</td> <td class="xl63" style="width: 87pt;" width="116">Jhonattan Vegas</td> <td class="xl66" style="width: 26pt;" width="34">0</td> </tr></table>
so in one cell has the golfer's name, in the next his score and so on. What I want to do is get the sum of the 5 smallest numbers accross each row for each participant in the pool. When I try an array formula I get a !#NUM error which I am assuming is because of the text. I would appreciate any help with a formula that would get me the sum of the 5 smallest scores. Thank you.

 

[Gerald Higgins]

What should the result be for your example, and why ?

 

[mheals]

The result should be the sum of the smallest 5 numbers, which in this example is -24. The reasoning is because the individual score for each pool participant is the sum of their 5 lowest scores for their golfers. Thanks in advance for the help.

 

[Gerald Higgins]

How do you get -24 ?

I make it
Rory McIlroy -7
Yong-Eun Yang -8
K.J.Choi -6
Angel Cabrera -2
Jhonattan Vegas 0

Total -23.

 

[mheals]

Sorry, you are correct. I was going off my sheet which is updated with realtime scores, not the example I used in the original posting, which was pasted. Do you have any insight into a formula that would work for this?

 

[T. Valko]

Sorry, you are correct. I was going off my sheet which is updated with realtime scores, not the example I used in the original posting, which was pasted. Do you have any insight into a formula that would work for this?

Try this array formula**:

=IF(COUNT(A1:J1)>=5,SUM(SMALL(IF(ISNUMBER(A1:J1),A1:J1),{1,2,3,4,5})),"")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Adjust the range to suit.

 

[mheals]

I just got a blank cell. I should note that the range of data is (B2:AE2) as each golfer and score are in separate cells.

 

[T. Valko]

I just got a blank cell. I should note that the range of data is (B2:AE2) as each golfer and score are in separate cells.

OK, what result do you get with this formula:

=COUNT(B2:AE2)

 

[jasonb75]

Try this array formula**:

=IF(COUNT(A1:J1)>=5,SUM(SMALL(IF(ISNUMBER(A1:J1),A1:J1),{1,2,3,4,5})),"")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Adjust the range to suit.

i.e.

=IF(COUNT(B2:AE2)>=5,SUM(SMALL(IF(ISNUMBER(B2:AE2),B2:AE2),{1,2,3,4,5})),"")

 

[mheals]

I get 0