1

Below is a mock of some of the data I have and I am trying to find a function that will return me with

a) the value with largest sum

b) the value with the second largest sum.... etc.

and below that is a mock of the results I want to show.

Been beating my head against a wall here since I can't modify the display of the data given the size and nature of the use of the spreadsheet

I want to achieve this using only excel formulae and not VBA/pivot tables et. al.

Germany 0.910579
Germany 0.411447
France  0.23819
France  0.260851
France  0.239344
UK      0.46665
UK      0.335537
Spain   0.401024
 ------------
1   Germany
2   UK
3   France
4   Spain

I found this link that shows me how to pick out the find the item with the largest sum in a list and this works if the data is all in one continous column broken up by the values I have my in Column A

excel example

D4={INDEX($A$1:$A$12,MATCH(LARGE(MMULT(TRANSPOSE(ROW($A$1:$A$12)^0),IFERROR((LOOKUP(ROW($A$1:$A$12),1/ISERR(-$A$1:$A$12)*ROW($A$1:$A$12),$A$1:$A$12)=TRANSPOSE($A$1:$A$12))*$A$1:$A$12,0)),C4),MMULT(TRANSPOSE(ROW($A$1:$A$12)^0),IFERROR((LOOKUP(ROW($A$1:$A$12),1/ISERR(-$A$1:$A$12)*ROW($A$1:$A$12),$A$1:$A$12)=TRANSPOSE($A$1:$A$12))*$A$1:$A$12,0)),0))}

http://forum.chandoo.org/threads/formula-challenge-022-find-the-item-with-the-largest-sum-in-a-list.12731/

And this was also very similar to this solution I found on SO, but this didnt account for repeating values and suggested the use of pivot tables but I am limited to formulaic solutions only

Extracting the top 5 maximum values in excel

Thanks

Community
  • 1
  • 1
TylerDurden
  • 1,632
  • 1
  • 20
  • 30
  • I am but what is your question? is there a problem with your formula? Please describe the actual problem –  Dec 16 '13 at 14:46
  • Have you tried to make a second table with the sorted data in a hidden sheet, and your third table that collects data from the hidden table on the main sheet? – stenci Dec 16 '13 at 14:47

1 Answers1

1

Assuming you have countries in A1:A8 and amounts in B1:B8 then list the positions (1,2,3,4 etc.) in A10 down and use this "array formula" in B10

=INDEX(A$1:A$8,MATCH(1,(COUNTIF(B$9:B9,A$1:A$8)=0)*(LARGE(SUMIF(A$1:A$8,A$1:A$8,B$1:B$8)*(MATCH(A$1:A$8,A$1:A$8,0)=ROW(A$1:A$8)-ROW(A$1)+1),A10)=SUMIF(A$1:A$8,A$1:A$8,B$1:B$8)),0))

confirm with CTRL+SHIFT+ENTER and copy down as far as you need. This will work even if 2 countries have exactly the same total (it will show the country that appears in the list first as higher than the other)

see example workbook here

barry houdini
  • 45,615
  • 8
  • 63
  • 81