Help optimizing array formula

E

el_ja

Board Regular
Joined
Nov 5, 2007
Messages
80
Hello again guys.
I have this array formula that works but it kills my excel for quite a while, I wanted to know if any of you guys can help making it faster.
First I have a matrix like this (It's for a construction, I know that A is 15% of the total cost and in the matrix it tells me the date where it's gonna be done.
So for the first item in row 3 I'm gonna spend in december 26 (40533*15%)+(40533*7,44%) = 9096,09)

<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">
</td><td style="text-align: right;;">15,00%</td><td style="text-align: right;;">7,44%</td><td style="text-align: right;;">7,54%</td><td style="text-align: right;;">9,12%</td><td style="text-align: right;;">15,35%</td><td style="text-align: right;;">11,19%</td><td style="text-align: right;;">4,11%</td><td style="text-align: right;;">13,73%</td><td style="text-align: right;;">10,72%</td><td style="text-align: right;;">5,80%</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;;">Cost</td><td style="font-weight: bold;text-align: right;;">A</td><td style="font-weight: bold;text-align: right;;">B</td><td style="font-weight: bold;text-align: right;;">C</td><td style="font-weight: bold;text-align: right;;">D</td><td style="font-weight: bold;text-align: right;;">E</td><td style="font-weight: bold;text-align: right;;">F</td><td style="font-weight: bold;text-align: right;;">G</td><td style="font-weight: bold;text-align: right;;">H</td><td style="font-weight: bold;text-align: right;;">I</td><td style="font-weight: bold;text-align: right;;">J</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">40.533 </td><td style="text-align: center;color: #FF0000;;">26-Dec-11</td><td style="text-align: center;color: #FF0000;;">26-Dec-11</td><td style="text-align: center;color: #FF0000;;">9-Jan-12</td><td style="text-align: center;color: #FF0000;;">16-Jan-12</td><td style="text-align: center;color: #FF0000;;">23-Jan-12</td><td style="text-align: center;color: #FF0000;;">30-Jan-12</td><td style="text-align: center;color: #FF0000;;">6-Feb-12</td><td style="text-align: center;color: #FF0000;;">13-Feb-12</td><td style="text-align: center;color: #FF0000;;">13-Feb-12</td><td style="text-align: center;color: #FF0000;;">20-Feb-12</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1.250 </td><td style="text-align: center;color: #FF0000;;">4-Apr-11</td><td style="text-align: center;color: #FF0000;;">4-Apr-11</td><td style="text-align: center;color: #FF0000;;">18-Apr-11</td><td style="text-align: center;color: #FF0000;;">25-Apr-11</td><td style="text-align: center;color: #FF0000;;">2-May-11</td><td style="text-align: center;color: #FF0000;;">9-May-11</td><td style="text-align: center;color: #FF0000;;">16-May-11</td><td style="text-align: center;color: #FF0000;;">23-May-11</td><td style="text-align: center;color: #FF0000;;">23-May-11</td><td style="text-align: center;color: #FF0000;;">30-May-11</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">31.961 </td><td style="text-align: center;color: #FF0000;;">26-Sep-11</td><td style="text-align: center;color: #FF0000;;">26-Sep-11</td><td style="text-align: center;color: #FF0000;;">10-Oct-11</td><td style="text-align: center;color: #FF0000;;">17-Oct-11</td><td style="text-align: center;color: #FF0000;;">24-Oct-11</td><td style="text-align: center;color: #FF0000;;">31-Oct-11</td><td style="text-align: center;color: #FF0000;;">7-Nov-11</td><td style="text-align: center;color: #FF0000;;">14-Nov-11</td><td style="text-align: center;color: #FF0000;;">14-Nov-11</td><td style="text-align: center;color: #FF0000;;">21-Nov-11</td></tr></tbody></table>
So I want to have in another sheet a matrix like this:
That shows me how much I have to pay in the dates from column A depending on the cost and then percentage of each concept (a,b,c)
<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">
</td><td style="text-align: right;;">15,00%</td><td style="text-align: right;;">7,44%</td><td style="text-align: right;;">7,54%</td><td style="text-align: right;;">9,12%</td><td style="text-align: right;;">15,35%</td><td style="text-align: right;;">11,19%</td><td style="text-align: right;;">4,11%</td><td style="text-align: right;;">13,73%</td><td style="text-align: right;;">10,72%</td><td style="text-align: right;;">5,80%</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">A</td><td style="font-weight: bold;text-align: right;;">B</td><td style="font-weight: bold;text-align: right;;">C</td><td style="font-weight: bold;text-align: right;;">D</td><td style="font-weight: bold;text-align: right;;">E</td><td style="font-weight: bold;text-align: right;;">F</td><td style="font-weight: bold;text-align: right;;">G</td><td style="font-weight: bold;text-align: right;;">H</td><td style="font-weight: bold;text-align: right;;">I</td><td style="font-weight: bold;text-align: right;;">J</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;color: #FF0000;;">4-Apr-11</td><td style="text-align: right;;">=Sheet1!A4*B7
</td><td style="text-align: right;;">{=SUM(IF(Sheet1!C:C=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;color: #FF0000;;">18-Apr-11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;color: #FF0000;;">25-Apr-11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: center;color: #FF0000;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;color: #FF0000;;">2-May-11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;color: #FF0000;;">9-May-11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;color: #FF0000;;">16-May-11</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>
Right now I can do it, but it's killing the program cause the dates go like that until 2012 so it's a lot of formulas.
{=SUM(IF(Sheet1!B:B=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))} This one gets copied down in sheet 2, in all the dates in column B.
Then this for column C, and so on
{=SUM(IF(Sheet1!C:C=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
el_ja said:
...

Right now I can do it, but it's killing the program cause the dates go like that until 2012 so it's a lot of formulas.
{=SUM(IF(Sheet1!B:B=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))} This one gets copied down in sheet 2, in all the dates in column B.
Then this for column C, and so on
{=SUM(IF(Sheet1!C:C=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}
Click to expand...

You might want to use dynamic named ranges instead of whole column references...

Define Lrow by means of Insert | Name | Define (or Formulas | Name Manager) as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Define Arange (or any other more convenient name) as referring to:

=Sheet1!$A$3:INDEX(Sheet1!$A:$A,Lrow)

Brange as:

=Sheet1!$B$3:INDEX(Sheet1!$B:$B,Lrow)

Crange as:

=Sheet1!$C$3:INDEX(Sheet1!$C:$C,Lrow)

Now we can invoke:

{=SUM(IF(Brange=Sheet2!A132,Arange*Sheet2!B$71,0))}

{=SUM(IF(Crange=Sheet2!A132,Arange*Sheet2!B$71,0))}

Note if these formulas are done on Sheet2, you can remove the Sheet2! prefix from the foregoing formulas.
 
Upvote 0
el_ja said:
Hello again guys.
I have this array formula that works but it kills my excel for quite a while, I wanted to know if any of you guys can help making it faster.
First I have a matrix like this (It's for a construction, I know that A is 15% of the total cost and in the matrix it tells me the date where it's gonna be done.
So for the first item in row 3 I'm gonna spend in december 26 (40533*15%)+(40533*7,44%) = 9096,09)

<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">15,00%</TD><TD style="TEXT-ALIGN: right">7,44%</TD><TD style="TEXT-ALIGN: right">7,54%</TD><TD style="TEXT-ALIGN: right">9,12%</TD><TD style="TEXT-ALIGN: right">15,35%</TD><TD style="TEXT-ALIGN: right">11,19%</TD><TD style="TEXT-ALIGN: right">4,11%</TD><TD style="TEXT-ALIGN: right">13,73%</TD><TD style="TEXT-ALIGN: right">10,72%</TD><TD style="TEXT-ALIGN: right">5,80%</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">Cost</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">A</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">B</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">C</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">D</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">E</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">F</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">G</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">H</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">I</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">J</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">40.533 </TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">26-Dec-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">26-Dec-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">9-Jan-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">16-Jan-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">23-Jan-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">30-Jan-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">6-Feb-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">13-Feb-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">13-Feb-12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">20-Feb-12</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1.250 </TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">4-Apr-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">4-Apr-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">18-Apr-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">25-Apr-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">2-May-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">9-May-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">16-May-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">23-May-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">23-May-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">30-May-11</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">31.961 </TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">26-Sep-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">26-Sep-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">10-Oct-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">17-Oct-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">24-Oct-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">31-Oct-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">7-Nov-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">14-Nov-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">14-Nov-11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">21-Nov-11</TD></TR></TBODY></TABLE>
So I want to have in another sheet a matrix like this:
That shows me how much I have to pay in the dates from column A depending on the cost and then percentage of each concept (a,b,c)
<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">15,00%</TD><TD style="TEXT-ALIGN: right">7,44%</TD><TD style="TEXT-ALIGN: right">7,54%</TD><TD style="TEXT-ALIGN: right">9,12%</TD><TD style="TEXT-ALIGN: right">15,35%</TD><TD style="TEXT-ALIGN: right">11,19%</TD><TD style="TEXT-ALIGN: right">4,11%</TD><TD style="TEXT-ALIGN: right">13,73%</TD><TD style="TEXT-ALIGN: right">10,72%</TD><TD style="TEXT-ALIGN: right">5,80%</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">A</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">B</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">C</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">D</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">E</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">F</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">G</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">H</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">I</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">J</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">4-Apr-11</TD><TD style="TEXT-ALIGN: right">=Sheet1!A4*B7

</TD><TD style="TEXT-ALIGN: right">{=SUM(IF(Sheet1!C:C=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">18-Apr-11</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">25-Apr-11</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">2-May-11</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">9-May-11</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">16-May-11</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD><TD style="TEXT-ALIGN: right">

</TD></TR></TBODY></TABLE>
Right now I can do it, but it's killing the program cause the dates go like that until 2012 so it's a lot of formulas.
{=SUM(IF(Sheet1!B:B=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))} This one gets copied down in sheet 2, in all the dates in column B.
Then this for column C, and so on
{=SUM(IF(Sheet1!C:C=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}
Click to expand...
You can replace those array formulas with simple SUMIFs that will only calculate the used range as opposed to the entire columns that the array is calculating.

{=SUM(IF(Sheet1!B:B=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}
Click to expand...

=SUMIF(Sheet1!B:B,Sheet2!A132,Sheet1!$A:$A)*Sheet2!B7$1

{=SUM(IF(Sheet1!C:C=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))}
Click to expand...

=SUMIF(Sheet1!C:C,Sheet2!A132,Sheet1!$A:$A)*Sheet2!B7$1
 
Upvote 0
Hi friends,

Let me get in and pose a question.

I have read in some book ( i think John Walkenback , not sure) that references to the entire column cause no damage to performance (Excel 2007 or higher)

On the other hand, one Excel MVP in this forum, i dont remember exactly who, has posted several times that is recommended avoid such references.

Where is the true?

M.
 
Upvote 0
Marcelo Branco said:
Hi friends,

Let me get in and pose a question.

I have read in some book ( i think John Walkenback , not sure) that references to the entire column cause no damage to performance (Excel 2007 or higher)

On the other hand, one Excel MVP in this forum, i dont remember exactly who, has posted several times that is recommended avoid such references.

Where is the true?

M.
Click to expand...
An ARRAY formula will calculate/evaluate EVERY cell that it references regardless of the Excel version.

This ARRAY formula entered in Excel 2007 will calculate/evaluate every cell in both coulmns A and B:

=SUM(IF(Sheet1!B:B=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))

That's 2,097,152 cells being calculated for just 1 formula.

The equivalent SUMIF formula will only calculate up to the used range even though you may reference the entire columns.

For example, if your last row of data is B100 then the effective used range is A1:B100.

=SUMIF(A:A,"X",B:B)

Even though we use entire columns as range references Excel effectively only evaluates this:

=SUMIF(A1:A100,"X",B1:B100)
 
Upvote 0
T. Valko said:
You can replace those array formulas with simple SUMIFs that will only calculate the used range as opposed to the entire columns that the array is calculating.



=SUMIF(Sheet1!B:B,Sheet2!A132,Sheet1!$A:$A)*Sheet2!B7$1



=SUMIF(Sheet1!C:C,Sheet2!A132,Sheet1!$A:$A)*Sheet2!B7$1
Click to expand...
Typos...

Both references to Sheet2!B7$1 should be Sheet2!B$71
 
Upvote 0
T. Valko said:
An ARRAY formula will calculate/evaluate EVERY cell that it references regardless of the Excel version.

This ARRAY formula entered in Excel 2007 will calculate/evaluate every cell in both coulmns A and B:

=SUM(IF(Sheet1!B:B=Sheet2!A132,(Sheet1!$A:$A*Sheet2!B7$1);0))

That's 2,097,152 cells being calculated for just 1 formula.

The equivalent SUMIF formula will only calculate up to the used range even though you may reference the entire columns.

For example, if your last row of data is B100 then the effective used range is A1:B100.

=SUMIF(A:A,"X",B:B)

Even though we use entire columns as range references Excel effectively only evaluates this:

=SUMIF(A1:A100,"X",B1:B100)
Click to expand...

Good explanation, tks.

And what about SUMPRODUCT - its considered an array-formula or not (not confirmed with CSE...)?

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
89
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
S
  • Solved
XLOOKUP with multiple criteria across multiple columns and rows
Replies
6
Views
1K
soriaul
S

Forum statistics

Threads
1,224,587
Messages
6,179,733
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top