HELP! Combine Large and Sum

[krislopmar]

Im trying to get the n largest value after a sum. For example, I have:

[pre]

Jan Feb Mar Apr Jun Jul ...
Country 1   23  31  11  43  18   9
Country 2   40  55  21  18   7  24
Country 3   14  31  45  6   29  14
...

[/pre]
Before I have always used lookup first and then a combination of Sum and Offset to find year to date values for a certain country, but now this is not possible as what I need is:

If cell A1 is February, I need a formula that first sums Jan + Feb for every country (up to 200) and then finds me the n largest of this sum results across all 200 countries in cell C5. Then in cell B5 I have to find the country that provided that amount. Cell A1 is variable and be from 1 to 12.


Any ideas will be hugely appreciated. Thanks a lot in advance!

 

[Debraj]

Hi Krislopmar,


Interesting problem.. !!!

Can you please post a sample file with expected output..


Regards,

Deb

 

[NARAYANK991]

Hi Kris ,


I am not sure I have understood you correctly , since you talk of B5 and C5 ; I cannot understand how B5 and C5 can be used for the output when there are 200 countries , and 12 months data in these columns / rows.


Anyway , if you want , you can try this :


=MAX(SUBTOTAL(9,(OFFSET(B3,ROW(1:3)-1,,1,A1))))


This will give you the maximum sum of the months specified in A1. ROW(1:3) will become ROW(1:200) if you have 200 countries' data.


=INDEX(A3:A5,MATCH(MAX(SUBTOTAL(9,(OFFSET(B3,ROW(1:3)-1,,1,A1)))),(SUBTOTAL(9,(OFFSET(B3,ROW(1:3)-1,,1,A1)))),0))


will give the country's name.


Both are array formulae , entered using CTRL SHIFT ENTER.


Courtesy an answer by Hui a few days back here :


http://chandoo.org/forums/topic/sum-the-total-of-greater-number-from-many-columns


Narayan

 

[krislopmar]

I have the output in a sheet:

No. Country Amount

1

2

3

...

20


Here I must find first the largest amount year to date (this case jan + feb), in column C for the largest (no.1) to the 20th largest (no.20). Then in column B I must find the correspondent country for that amount. Bit tricky as well since I do not have totals in the data sheet. Hope to find a way around it.


In another sheet I have all the data

Enero Febrero

ALEMANIA 523 -263

ARABIA SAUDITA 284 1.282

ARGELIA 0 77

ARGENTINA 147 3.022

AUSTRIA 89 116

BELGICA 1.833 6.690

BRASIL 205 705

BULGARIA 0 994

CANADA 309 1.003

CHINA 1.009 73


I hope this clarifies my initial post. Thanks Narayank991, I tried your solution, but it doesnt add up the jan+feb amount, just gives me the largest amount of the first month...

 

[Haseeb A]

Hello Kris,


See the attached file on SkyDrive:


Link:


http://sdrv.ms/10wczG6


There are 3 methods, 1) with MMULT, 2) with OFFSET, 3) with Helper column


Hope this helps;

Haseeb