# Thread: Sum last 10 entries in a row

1. ## Sum last 10 entries in a row

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.

2. Hi,
Can you post your datas in attachment ?
Eric

3. Hopefully you can see the file I attached?

Originally Posted by terryod
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.

4. @ 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

5. 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.

Originally Posted by terryod
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.

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.

7. 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?

8. 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.

9. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•