Sum(small excluding text

mheals

New Member
Joined
Apr 8, 2011
Messages
8
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mheals

New Member
Joined
Apr 8, 2011
Messages
8
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Apr 8, 2011
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Apr 8, 2011
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,832
Office Version
  1. 2019
Platform
  1. Windows
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})),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top