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]

Welcome to the board.

Based on your sample of data, try the below array formula (confirm with ctrl+shift+enter):
=SUM(IF(Net_sales>=LARGE(Net_sales,3),Total_Income))

 

[thakkarvamar]

Thanks a lot .. it works well

 

[thakkarvamar]

It causes an error if there are less then 3 rows ..

=SUMPRODUCT(--(IFERROR(RANK(Net_Sales,Net_Sales)<3,0)),Total_Income)</pre>This code works for my problem ..Although I believe there might be way to check error in the previous solution also ..

thanks a lot again

 

[njimack]

What would you want returned if there are less than 3 rows? The Total of Total_Income?

 

[thakkarvamar]

Ya... sum of total incomes of whatever rows are available

 

[njimack]

Try this:
=IF(ROWS(Net_Sales)>=3,SUM(IF(Net_Sales>=LARGE(Net_Sales,3),Total_Income)),SUM(Total_Income))

 

[thakkarvamar]

Does not work .. returns #Num! for less than 3 rows..

Try this:
=IF(ROWS(Net_Sales)>=3,SUM(IF(Net_Sales>=LARGE(Net_Sales,3),Total_Income)),SUM(Total_Income))

 

[njimack]

Does not work .. returns #Num! for less than 3 rows..

What does the below formula return?
=ROWS(Net_Sales)

 

[thakkarvamar]

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