I've looked in a variety of books and a variety of places - but I
haven't quite figured out if it's possible to do what I want.

The problem involves two separate sheets of a file - for simplicity,
I'll call them eventa and totals.

Eventa has a series of scores in column ah, starting with row 5
(there's already a predefined name for this range).

Each score is related back to a particular team code, which is derived
from a left formula from the personal code in column a and stored in
column ae (basically, the first two numbers of a 4-digit code).

So, for example:

If the code in a5 is 1004, the score in ah5 might be 7, and the team
code in ae5 would be 10.

i.e., in eventa:
col a ae ah

1004 10 8
1009 10 7
1013 10 2
1016 10 4
1018 10 5
1110 11 3

What I'd like to do is to take the top 4 scores for each team code
(the multiple conditions) and store it in the totals sheet.

Currently, I use sumif's in the totals sheet that look something like
this:
Row 5 -- sumif(eventa!ah$5:ah$200,s5,eventa! af$5:af$200), where
s5=the teamcode (from the same left formula below):
So, if the teamcode was 10, Row 5 would equal 8+7+2+4+5 = 26.

What I'd like is for that total to be 8+7+4+5 = 24 (basically not
counting the 2).

I"ve tried various combinations of sumproduct, sumif, and large
functions without success. Any help in pushing me in the right
direction would be much appreciated.

Thanks in advance!

Email address is munged - take the left side, add "ml," and then
replace the right side with earthlink.net.

Michael,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(LARGE((eventa!$AH$5:$AH$200)*(eventa!$AF$5:$A F$200=S5),{1,2,3,4}))

HTH,
Bernie
MS Excel MVP


"Michael Dreher" wrote in message
...
I've looked in a variety of books and a variety of places - but I
haven't quite figured out if it's possible to do what I want.

The problem involves two separate sheets of a file - for simplicity,
I'll call them eventa and totals.

Eventa has a series of scores in column ah, starting with row 5
(there's already a predefined name for this range).

Each score is related back to a particular team code, which is derived
from a left formula from the personal code in column a and stored in
column ae (basically, the first two numbers of a 4-digit code).

So, for example:

If the code in a5 is 1004, the score in ah5 might be 7, and the team
code in ae5 would be 10.

i.e., in eventa:
col a ae ah

1004 10 8
1009 10 7
1013 10 2
1016 10 4
1018 10 5
1110 11 3

What I'd like to do is to take the top 4 scores for each team code
(the multiple conditions) and store it in the totals sheet.

Currently, I use sumif's in the totals sheet that look something like
this:
Row 5 -- sumif(eventa!ah$5:ah$200,s5,eventa! af$5:af$200), where
s5=the teamcode (from the same left formula below):
So, if the teamcode was 10, Row 5 would equal 8+7+2+4+5 = 26.

What I'd like is for that total to be 8+7+4+5 = 24 (basically not
counting the 2).

I"ve tried various combinations of sumproduct, sumif, and large
functions without success. Any help in pushing me in the right
direction would be much appreciated.

Thanks in advance!

Email address is munged - take the left side, add "ml," and then
replace the right side with earthlink.net.