Conditional Sum based on criteria on two sheets

[Myrko]

Hi,


I have two spreadsheets, each containing two columns - Company ID and Profit (Sheet1), the other one contains Company ID and Sector(Sheet2).


I am building third sheet which will contain a calculation. Let's say I'll put a drop down menu in A1 which will allow user to choose Sector. Then, I want in A2 a formula which would sum profits of the top 10 companies by profits from sheet1 - only those companies which met Sector criteria (selected in A1).


Any thoughts? I prefer solving it without importing Sector data to the second sheet and then doing simple Sumproduct/Large combo of functions.


I have tried with:


{=SUMPRODUCT(LARGE(IF(IF(Sheet2!B:B=A1,Sheet2!A:A)=Sheet1!A:A,Sheet1!B:B),ROW(INDIRECT("1:10"))}


But it showed 0 as a result (I am doing something wrong apparently, since I've manually checked that 0 is not the actual result of what I'm trying to calculate).


Thanks

 

[jarjarbingie]

Hi,


I have two spreadsheets, each containing two columns - Company ID and Profit (Sheet1), the other one contains Company ID and Sector(Sheet2).


I am building third sheet which will contain a calculation. Let's say I'll put a drop down menu in A1 which will allow user to choose Sector. Then, I want in A2 a formula which would sum profits of the top 10 companies by profits from sheet1 - only those companies which met Sector criteria (selected in A1).


Any thoughts? I prefer solving it without importing Sector data to the second sheet and then doing simple Sumproduct/Large combo of functions.


I have tried with:


{=SUMPRODUCT(LARGE(IF(IF(Sheet2!B:B=A1,Sheet2!A:A)=Sheet1!A:A,Sheet1!B:B),ROW(INDIRECT("1:10"))}


But it showed 0 as a result (I am doing something wrong apparently, since I've manually checked that 0 is not the actual result of what I'm trying to calculate).


Thanks

=SUM(LARGE(SUMIF(Sheet1!$A$2:$A$100,IF(Sheet2!$B$2:$B$100=$A$1,Sheet2!$A$2:$A$100,""),Sheet1!$B$2:$B$100),ROW(INDIRECT("1:10")))) press CTRL+SHIFT+ENTER