# Archery & Excel: Large, Offset, Sum = Missed

#### Shaz_au

##### New Member
Hi,
I'm hoping a guru here can help before my hairline recedes further! I've been trying to create an electronic score sheet for an Archery championship. At each event a competitor gets a score that consists of a total and the number of bullseyes they hit. Example: a total of 99 and 6 bullseyes. They have 20 events for the year, and the best 12 events' totals count towards your championship aggregate.

I've found I can return the sum for the best 12 event totals using the following formula:
SUM(LARGE(J4:J26,{1,2,3,4,5,6,7,8,9,10,11,12}))

What I can't work out is how to return the associated bullseye counts for those best 12 event totals!

I've tried using: Sum(large(offset(j4:j26,0,1),{1,2,3,4,5,6,7,8,9,10,11,12} but this returns the sum of the top 12 bullseye counts for the year rather than returning the sum of the bullseyes that relate to the events with the highest total score.

I have looked at some of the other posts here regarding the offset and large formulas but I couldn't find any that with my skills I could adapt to work. Any help would be greatly appreciated.

Thanks,
Shaz

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

Try:

=SUM(IF(J4:J26>=LARGE(J4:J26,12),K4:K26))

confirmed with Ctrl+Shift+Enter. It's an array formula that Excel will surround with curly braces {} if entered correctly.

#### Shaz_au

##### New Member
Hi Andrew,
Thank you for the welcome and the reply.
I used the shift+ctrl+enter for the formula and it has calculated a number but it doesn't appear to be right. with my sample data it is returning 172 when I was expecting to it to return 132.

The sample data:
column J:
 99 100 96 93 98 98 0 95 94 99 98 100 100 97 98 0 100 100 99 99

<colgroup><col></colgroup><tbody>
</tbody>

column: K
 12 11 7 7 7 11 0 6 5 10 7 13 12 8 9 0 13 13 10 11

<colgroup><col></colgroup><tbody>
</tbody>

Thanks!

#### Andrew Poulsom

##### MrExcel MVP
For me the formula returns 139:

Excel 2010
JKLM
4991213912
51001111
6967FALSE
7937FALSE
89877
9FALSE
10981111
1100FALSE
12956FALSE
13945FALSE
14991010
15FALSE
169877
171001313
181001212
19978FALSE
209899
21FALSE
2200FALSE
231001313
241001313
25991010
26991111
Sheet1
Cell Formulas
RangeFormula
M4=IF(J4>=LARGE(J\$4:J\$26,12),K4)
L4{=SUM(IF(J4:J26>=LARGE(J4:J26,12),K4:K26))}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Shaz_au

##### New Member
Thanks again for the reply! I apologise, your formula was returning 139 for me too, unfortunately I still think it should be 132. It looks to me like it has included 13 events above, probably because there was duplicate totals.

Also, where there is duplicate totals the formula needs include the total that has the highest bullseye count.

Confused yet?

#### Andrew Poulsom

##### MrExcel MVP
So what should the result be for your sample data? Which specific numbers in column K should be summed?

#### Shaz_au

##### New Member
The top 12 events are:

 100 11 100 13 100 12 100 13 100 13 99 12 99 10 99 10 99 11 98 7 98 11 98 9

<colgroup><col span="2"></colgroup><tbody>
</tbody>

The totals should be:
 1190 132

<colgroup><col span="2" width="64"></colgroup><tbody>
</tbody>

I think the formula is picking up that there was two 98 with 7s and including both in the bullseye count.

#### Andrew Poulsom

##### MrExcel MVP

=SUM(RIGHT(LARGE((J\$4:J\$26&TEXT(K\$4:K\$26,"00"))+0,ROW(A1:A12)),2)+0)

confirmed with Ctrl+Shift+Enter.

#### Shaz_au

##### New Member
Wow! that works, I will test it with some more data and confirm back! Thank you so much for your help

I have no idea how this formula works, any chance you can give me a brief explanation please?

Thanks again,
Shaz

#### Andrew Poulsom

##### MrExcel MVP
To see how the formula works use the Evaluate Formula tool.

Replies
4
Views
642
Replies
3
Views
562
Replies
2
Views
819
Replies
2
Views
1K
Replies
0
Views
1K

1,191,671
Messages
5,987,956
Members
440,121
Latest member
eravella

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back