Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an contest?
On Fri, 7 Aug 2009 15:24:45 -0700 (PDT), Håkan 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 multipost. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an contest?
Håkan 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. Please don't multi-post. It makes following the threads rather difficult. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an contest?
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 "Håkan" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an contest?
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 "Håkan" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an contest?
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 "HÃ¥kan" 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum the 3 best scores for every country in an contest?
On 8 Aug, 06:40, Shane Devenshire
wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
contest | Excel Worksheet Functions | |||
typing 'Up Country' ends up as Up Country' ? | Excel Discussion (Misc queries) | |||
excel golf scores, how do I add the scores for all par 3's etc | Excel Worksheet Functions | |||
Can Excel randomly pick one winner out of 100 contest entries? | Excel Discussion (Misc queries) | |||
How can I total the top 9 scores of 12 weeks in a sporting contest | Excel Discussion (Misc queries) |