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

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

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

On Fri, 7 Aug 2009 15:38:03 -0700 (PDT), Persson wrote:

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

See answer in other NG. Please don't multi-post
--ron

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

Persson wrote:
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

Answered in MPE.

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

On 8 Aug, 01:11, Ron Rosenfeld wrote:
On Fri, 7 Aug 2009 15:38:03 -0700 (PDT), Persson wrote:
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

See answer in other NG. *Please don't multi-post
--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 starting
within 2 hours.

Once again, thank you.

Regards Dan