# Sum last 10 entries in a row

#### terryod

##### New member
Hi
I'm a novice excel user. I need to make a spreadsheet for clay target club. I want to add the last 10 entries in a row to give the person a handicap. Can anyone help? Can't be the last 10 rows, because they don't all shoot every week. Must be the last 10 entries.

#### EricPoirier

##### New member
Hi,
Can you post your datas in attachment ?
Eric

#### terryod

##### New member
Hopefully you can see the file I attached?

Hi
I'm a novice excel user. I need to make a spreadsheet for clay target club. I want to add the last 10 entries in a row to give the person a handicap. Can anyone help? Can't be the last 10 rows, because they don't all shoot every week. Must be the last 10 entries.

#### Attachments

• Copy - broome field and game score sheet current version 2013.xlsx
30.2 KB · Views: 37

#### Sixthsense

##### New member
@ terryod,

Please give some description about your data and with cell/Range/Sheet references and expected results. So that we can able to get better understanding of your expectation

#### terryod

##### New member
In column A there is a list of participants. Their scores are listed to the right under a column header for the date of the shoot. We just add an extra column for each new week. I just want to add the total of the last 10 scores, to give a handicap.

Hi
I'm a novice excel user. I need to make a spreadsheet for clay target club. I want to add the last 10 entries in a row to give the person a handicap. Can anyone help? Can't be the last 10 rows, because they don't all shoot every week. Must be the last 10 entries.

#### NoS

##### New member

Because your dates/scores are columns H thru IW, the formula I can see (without an account there) adapts to something like this

=SUM(IF(COLUMN(H2:IW2)>=LARGE(IF(H2:IW2>0,COLUMN(H2:IW2)),10),H2:IW2)) confirmed with Control+Shift+Enter.

#### terryod

##### New member
Thanks NoS the above formula works for all participants with 10 shots or more recorded to their name - awesome

Can I add an "If" and put the formula above in the the positive, to avoid the #NUM! result for those with less than 10 shots?

I did try, but couldn't make it work? Think it's got to do with the ctrl+shift+enter?

#### NoS

##### New member
Hi terryod

Hopefully some of the "Formula Wizards" will chime in with a solution.

I just did a quick Google search for what I thought an interesting question and indicated what I found.

What result would you want for a participant with less than 10 shots?
Is the formula you're after to go in column E?
Do you really need to go to column XDF for your calculations? At 2 weeks per column, the date in that column would be over 600 years from now.

My tendency for this would be a macro with a couple of loops but I'm sure a formula using Excel's built in functions would be better and faster, so, like you I'm waiting to see what others will suggest.

#### Sixthsense

##### New member
In E2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=SUM(INDEX(H2:IW2,,MATCH(MIN(IFERROR(LARGE(IF(H2:IW2<>"",COLUMN(H2:IW2)),ROW(\$1:\$10)),9^9)),COLUMN(H2:IW2),0)):IW2)

Drag it down...

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.