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

 

[Eric W]

You're on the right track:

=SUM(SUMIF(Sheet1!A:A,IF(Sheet2!B2:B8=A1,Sheet2!A2:A8,"zzzz"),Sheet1!B:B))
confirmed with Control+Shift+Enter.

 

[Myrko]

Thanks Eric!

I am now working on two more formulas - sum of top 10 and top 10% (by profit).

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

This is my attempt for top 10 sumif but it didn't work again I will know how to apply changes so it shows top 10% instead, just need a review of this one...

Thanks once again in advance!

 

[Eric W]

There may be a better way, but this works:

=SUM(LARGE(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$30&"|"&A1,Sheet2!$A$2:$A$20&"|"&Sheet2!$B$2:$B$20,0)),Sheet1!$B$2:$B$30),ROW(INDIRECT("1:10"))))
with CSE.

And I really do recommend not using full column references. It can really slow down your sheet. Even if you use 1000, or 10000, or 100000, that's better than having Excel look at over a million rows. Pick the highest number you expect you can reach and add 10%.

 

[Myrko]

Do you think it would help if I added COUNTA as bottom line for ranges or it would add to the complexity/length of calculation in another way?

 

[Eric W]

Sure, you can use COUNTA. Assuming you have no empty lines in your data, that's a good way to find the end. You can set up a dynamic range even. Go to the Formulas tab > Name Manager > New > and give it a name of ID_Profit with a formula of

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

repeat with a name of ID_Sector, and change Sheet1 to Sheet2. Then your formula would be:

=SUM(LARGE(IF(ISNUMBER(MATCH(INDEX(ID_Profit,,1)&"|"&A1,INDEX(ID_Sector,,1)&"|"&INDEX(ID_Sector,,2),0)),INDEX(ID_Profit,,2)),ROW(INDIRECT("1:10"))))
with CSE.

 

[Myrko]

I have decided to manually decrease range sizes... However, it appears that I will need two more formulas as an upgrade to the one you've provided:
-One additional condition - profit smaller than value X
-Two additional conditions - profit greater than y but smaller than X
I have tried to manipulate your provided formula but it returns zero
Thanks a bunch in advance, again!

 

[Eric W]

I'm not sure which formula you want upgraded. Is it the one from post #2 ? You want a sum of all the profits on Sheet1, if they're in the right section, and (the upgrade) the values are in a given range?

 

[Myrko]

Both post #2 and the Top 10 one, and yes about the conditions.

Also, I could use an upgrade of Range defined from both sides (more than, less than) as an addition to the previously mentioned two formulas.

So, at this moment, I believe I need:
Sum of all Profits which meet criteria of - Profit < than X (X is defined value) and meets Sector Criteria
Sum of top 10 companies' profits with the same conditions

Copy of above mentioned two formulas, same conditions plus profit > Y as an addition

Then I believe I'll have everything I need Sorry for adding more things, it is greatly appreciated!!!

 

[Eric W]

I think this is all you asked for:

	D	E	F
a
Sum of profits matching sector
Sum of 10 top profits matching sector
Sum of profits matching sector, between E1 and F1 values
Sum of 10 top profits matching sector, between E1 and F1 values
Number of companies included in D5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]191[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]181[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]119[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]119[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!$A$2:$A$30&"|"&D1,Sheet2!$A$2:$A$20&"|"&Sheet2!$B$2:$B$20,0))),--(Sheet1!$B$2:$B$30>=E1),--(Sheet1!$B$2:$B$30<=F1),Sheet1!$B$2:$B$30)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=SUM(SUMIF(Sheet1!A2:A30,IF(Sheet2!B2:B20=D1,Sheet2!A2:A30,"zzzz"),Sheet1!B2:B20))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]{=SUM(LARGE(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$30&"|"&D1,Sheet2!$A$2:$A$20&"|"&Sheet2!$B$2:$B$20,0)),Sheet1!$B$2:$B$30),ROW(INDIRECT("1:10"))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]{=SUM(IFERROR(LARGE(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$30&"|"&D1,Sheet2!$A$2:$A$20&"|"&Sheet2!$B$2:$B$20,0))*(Sheet1!B$2:$B$30>=E1)*(Sheet1!B$2:$B$30<=F1),Sheet1!$B$2:$B$30),ROW(INDIRECT("1:10"))),0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]{=SUM(COUNTIFS(Sheet1!A2:A30,IF(Sheet2!B2:B20=D1,Sheet2!A2:A30,"zzzz"),Sheet1!B2:B30,">="&E1,Sheet1!B2:B30,"<="&F1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



For the purpose of this example, the sector is in D1, the low value (Y) is in E1, and the high value (X) is in F1. You should change those to your actual locations. I didn't create the formulas for <x only,="" since="" you="" can="" always="" put="" -9e99="" in="" e1="" and="" achieve="" the="" same="" result.

Let me know how they work.
</x>