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

H

Håkan

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
 
R

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

See answer in other NG. Please don't multipost.
--ron
 
S

smartin

Håkan said:
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. Please don't multi-post. It makes following the threads
rather difficult.
 
A

Ashish Mathur

Hi,

Try this array formula (Ctrl+Shift+Enter). The numbers are in range L4:L13
and countries are in range M4:M13. L16 holds the country name

=SUM(LARGE((M4:M13=L16)*(L4:L13),{1,2,3}))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
A

Ashish Mathur

Hi,

You can also use a pivot table for this. Drag Country to the row area,
Score to the row area again (should appear after country). Drag score (once
again) to the Data Area. Now in Excel 2007, go the filter drop down in the
Score (in the row area) and under Value filters, select Top 10. Change 10
to 3. Now click on OK

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
S

Shane Devenshire

You can use the array formula

=SUM(LARGE(IF(B1:B10=C1,A1:A10,""),ROW(1:3)))
or the non-array
=SUMPRODUCT(LARGE((B1:B10=C1)*(A1:A10),ROW(1:3)))

the advantage in using ROW(1:3) verses {1,2,3} is that if you wanted to find
the 50 largets items {} would become extremely long where as ROW would not.
 
H

Hakan

You can use the array formula

=SUM(LARGE(IF(B1:B10=C1,A1:A10,""),ROW(1:3)))
or the non-array
=SUMPRODUCT(LARGE((B1:B10=C1)*(A1:A10),ROW(1:3)))

the advantage in using ROW(1:3) verses {1,2,3} is that if you wanted to find
the 50 largets items {} would become extremely long where as ROW would not.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire








- Visa citerad text -



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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top