How to sum the 3 best scores for every country in a contest?

[Persson]

To sum the 3 highest numbers, following functions can be used in a
really nice combination:
=SUM(LARGE(A1:A100, {1,2,3} ) )
But how to add an condition to it?

Example:
Column A below are the scores for induvudual persons in a contest.
Column B below is the country they are competing for
..
How to add the sum of the 3 best scores for each country ?
The correct answer should be 213+199+187=599 for USA and
198+158+135=491 for Canada.
A B
141 USA
199 USA
135 CAN
187 USA
158 CAN
145 USA
213 USA
198 CAN
120 USA
180 CAN


Really looking forward for the experts to crack this one. Preferably
without using VB.


Regards Dan

 

[Ron Rosenfeld]

To sum the 3 highest numbers, following functions can be used in a
really nice combination:
=SUM(LARGE(A1:A100, {1,2,3} ) )
But how to add an condition to it?

Example:
Column A below are the scores for induvudual persons in a contest.
Column B below is the country they are competing for
.
How to add the sum of the 3 best scores for each country ?
The correct answer should be 213+199+187=599 for USA and
198+158+135=491 for Canada.
A B
141 USA
199 USA
135 CAN
187 USA
158 CAN
145 USA
213 USA
198 CAN
120 USA
180 CAN


Really looking forward for the experts to crack this one. Preferably
without using VB.


Regards Dan

I believe you have made an error in your "correct answer". The three highest
scores for Canada would seem to be 198+180+158=536, unless I am
misunderstanding your definition of "best score".

If I am correct, then one formula would be:

=SUMPRODUCT(LARGE((Country=D2)*Score,{1,2,3}))

Where D2 contains USA (or CAN), and Country & Score are the named ranges
containing the data.

e.g. Score = A2:A11
Country = B2:B11

--ron

 

[Persson]

I believe you have made an error in your "correct answer".  The three highest
scores for Canada would seem to be 198+180+158=536, unless I am
misunderstanding your definition of "best score".

If I am correct, then one formula would be:

=SUMPRODUCT(LARGE((Country=D2)*Score,{1,2,3}))

Where D2 contains USA (or CAN), and Country & Score are the named ranges
containing the data.

e.g. Score = A2:A11
   Country = B2:B11

--ron

Hi

Thank you all for the great solution! Sorry I had to multipost, but
this was a priority 1 to get an anser quick, the contest is itarting
within 2 hours.

Once again, thank you.

Regards Dan

 

[Ron Rosenfeld]

Hi

Thank you all for the great solution! Sorry I had to multipost, but
this was a priority 1 to get an anser quick, the contest is itarting
within 2 hours.

Once again, thank you.

Regards Dan

You're welcome and thanks for the feedback.

With most newsreaders, you should be able to cross-post instead. Then, instead
of seeing multiple, identical posts in different NG's, I would only see a
single post in the first NG I run across your post in. My response can then
get posted to all of the NG's in which you cross-posted.
--ron