Problem with using index, match and large functions together

[thakkarvamar]

My data is in 2 columns like this

Net sales total income
co1
co2
..
..

I want to find out the top 3 companies as per net sales. I want the sum of total incomes of these 3 companies.

I have written this formula to achieve my objective

IFERROR(INDEX(Total_income,MATCH(LARGE(Net_sales,1),Net_sales,0)),0)+IFERROR(INDEX(Total_income,MATC H(LARGE(Net_sales,2),Net_sales,0)),0)+IFERROR(INDEX(Total_income,MATCH(LARGE(Net_sales,3),Net_sales, 0)),0)

It works fine when net sales numbers for all companies are unique. But if any 2 companies have equal net sales.. it picks total income of same company twice.
Also I can add a column where I rank the companies as per net sales and use this column to solve my problem. But, I don't want to add any column to my data.

<table border="0" cellpadding="0" cellspacing="0" width="215"><col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64"> </td> <td class="xl63" style="border-left:none;width:48pt" width="64">Net sales</td> <td class="xl63" style="border-left:none;width:65pt" width="87">Total income</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">co1</td> <td class="xl63" style="border-top:none;border-left:none" align="right">100</td> <td class="xl63" style="border-top:none;border-left:none" align="right">100</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">co2</td> <td class="xl63" style="border-top:none;border-left:none" align="right">200</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">co3</td> <td class="xl63" style="border-top:none;border-left:none" align="right">150</td> <td class="xl63" style="border-top:none;border-left:none" align="right">150</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">co4</td> <td class="xl63" style="border-top:none;border-left:none" align="right">200</td> <td class="xl63" style="border-top:none;border-left:none" align="right">200</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">co5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">300</td> <td class="xl63" style="border-top:none;border-left:none" align="right">300</td> </tr> </tbody></table>
The output of my formula comes to 300 + 1000 +1000 = 2300
It should actually come to 300 + 1000 + 200 = 1500
thanks in advance

 

[njimack]

1048575 .. last row.. i have named the range till last row as i am not sure how many companies will appear in my file

Use dynamic ranges - see http://contextures.com/xlNames01.html#Dynamic