Array sumif - need an assistance

[Myrko]

Hi,

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

I am building third sheet which will contain 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 all profits from sheet1 - for all IDs which meet Sector criteria.

Any thoughts? I prefer solving it without importing Sector data to the second sheet and then doing simple sumif.

I have tried with an array function (descriptive form of function, not using valid references as I am in a kind of a rush):
{=sumif(Sheet1!A:A,if(Sheet2!B:B=A1,Sheet2!A:A),Sheet1!B:B)}

But it didn't seem like it worked

Thanks in advance

 

[Myrko]

Marvelous!

I've forgotten that I have to do averages too...

I have tried to modify Sum of profits of the companies which meet sector criteria but numbers are not right again...

I was able to put together SUM and COUNT (SUM/COUNT) to get it to work but I am not sure if it's most efficient way (I want to make it runs as fast as possible).

Is there simple way to transform D2 (sum of profits that meet criteria SECTOR) and D4 (sum of profits that meet criteria SECTOR, and profit range) formulas to show averages with the same criteria or do you think it would be similar to the sum/count in terms of efficiency?

Thanks for the 1000th time

 

[Eric W]

Yes, you just need to create SUM/COUNT formulas for D2 and D4. There really isn't any other way to get an accurate average. Both of those formulas are easy to convert to a COUNT formula (for the SUMPRODUCT, just remove the last range, for the other change the SUMIFS to a COUNTIFS). Let me know if you need help with that.

Glad to help!