Sum across multiple tabs, criteria on each tab

Sharon in Denver · Sep 15, 2011

I have over a hundred tabs that are identical in format and data type. Each tab belongs to one of two categories, A or B. On a summary tab with the same formats all the others, I have in corresponding cells the formula =Sum('first:Last'I4) for many columns and many rows. What I'd like to do is create two more summary tabs (A and B) and be able to perform that same Sum, but based on the criteria on each tab in H1. I've researched Sumproduct(sumif and I don't think that is what I need, since my criteria is on the individal tabs, not a separate, summary tab. But I'm not sure.

I would prefer to do this with a formula, if at all possible.

Thanks in advance!

Sharon in Denver · Sep 20, 2011

I'd like to thank Biff and Mark both for their work on this particularly snarly issue! Mark, your formula worked just fine, but with the size of my file, it was just a little slower than Biff's, so I used his.

Thank you for being such troopers, both of you!

-Sharon

markmzz · Sep 20, 2011

Sharon in Denver said:
I'd like to thank Biff and Mark both for their work on this particularly snarly issue! Mark, your formula worked just fine, but with the size of my file, it was just a little slower than Biff's, so I used his.

Thank you for being such troopers, both of you!

-Sharon

Sharon,

I'm happy that your problem was solved. This is that the most important thing.

And thanks for the feedback.

Markmzz

markmzz · Sep 20, 2011

T. Valko said:
If they insert new rows/columns on the sheet that holds the formula then that can cause those formulas to fail.

ADDRESS(ROW(I3),COLUMN(I3))

Is not a very good method.

T. Valko,

Only for understand.

Look at this:

Markmzz's Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(I3),COLUMN(I3)))))

ADDRESS(ROW(I3),COLUMN(I3))

T. Valko's Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(A$1:I3),COLUMNS($A3:I3)))))

ADDRESS(ROWS(A$1:I3),COLUMNS($A3:I3))

I think that here we have the same problem.

Or I'm wrong?

Markmzz

T. Valko · Sep 20, 2011

markmzz said:
T. Valko,

Only for understand.

Look at this:

Markmzz's Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(I3),COLUMN(I3)))))

ADDRESS(ROW(I3),COLUMN(I3))

T. Valko's Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(A$1:I3),COLUMNS($A3:I3)))))

ADDRESS(ROWS(A$1:I3),COLUMNS($A3:I3))

I think that here we have the same problem.

Or I'm wrong?

Markmzz

On the sheet that contains this formula, if you insert a new column before column I then in your formula the reference:

COLUMN(I3) = 9

Will change to:

COLUMN(J3) = 10

Causing the formula to miss the first cell to be calculated.

Same thing if you were to insert a new row before row 3:

ROW(I3) = 3

Will change to:

ROW(I4) = 4

If you use COLUMNS and ROWS like I did then those references will still refer to row 3 and column 9:

COLUMNS($A3:I3) = 9

Will change to:

COLUMNS($B3:J3) = 9

ROWS(A$1:I3) = 3

Will change to:

ROWS(A$2:I4) = 3

It's also not a good idea to use the COLUMN() and ROW() function without an argument for the same reason.

Of course, if you know for certain that you will never be inserting new rows/columns then those methods would be OK. However, we don't that because this is not our file and the cost to make the formula more robust is negligible.

markmzz · Sep 20, 2011

Sorry, but if I insert a column between your range (A1:I3) we have the same problem.

Look at this examples:

"If they insert new rows/columns on the sheet that holds the formula then that can cause those formulas to fail."

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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><TH>L</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</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: center; FONT-WEIGHT: bold">C</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</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: center"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col01</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col02</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col03</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,220</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,006</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,351</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,059</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">779</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">938</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,224</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,467</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,267</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">540</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">257</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,223</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">776</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">970</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,453</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">928</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,316</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">294</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,579</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,256</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,084</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,667</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,745</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,011</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">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: center"></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="TEXT-ALIGN: center; COLOR: #161120">12</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: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,220</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,006</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,351</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,059</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">779</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">938</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,224</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,467</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,267</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">540</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">257</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,223</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">776</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">970</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,453</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">928</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,316</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">294</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,579</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,256</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,084</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,667</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,745</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,011</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR></TBODY></TABLE>Master-Before

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(A$1:I3),COLUMNS($A3:I3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>J3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(B$1:J3),COLUMNS($A3:J3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>K3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(C$1:K3),COLUMNS($A3:K3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(I3),COLUMN(I3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>J13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(J3),COLUMN(J3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>K13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(K3),COLUMN(K3)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>CPool</TH><TD style="TEXT-ALIGN: left">={"Sheet001","Sheet002","Sheet003"}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
The sheet after a new column D.

Excel 2007

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center; FONT-WEIGHT: bold">C</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col01</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col02</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col03</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,006</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,351</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">779</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">938</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,467</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,267</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">257</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,223</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">970</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,453</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,316</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">294</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,256</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,084</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,745</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">1,011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">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; BACKGROUND-COLOR: #ff0000"></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: center"></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="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,006</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,351</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">779</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">938</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,467</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,267</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">257</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,223</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">970</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,453</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,316</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">294</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,256</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,084</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000"></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: center"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,745</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1,011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR></TBODY></TABLE>Master-After

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>J3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(A$1:J3),COLUMNS($A3:J3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>K3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(B$1:K3),COLUMNS($A3:K3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>L3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(C$1:L3),COLUMNS($A3:L3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>J13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(J3),COLUMN(J3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>K13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(K3),COLUMN(K3)))))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>L13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(L3),COLUMN(L3)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>CPool</TH><TD style="TEXT-ALIGN: left">={"Sheet001","Sheet002","Sheet003"}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Look at values of the Col03. Where I'm wrong?

Markmzz

markmzz · Sep 20, 2011

Look at this:

T. Valko said:
On the sheet that contains this formula, if you insert a new column before column I then in your formula the reference:

You didn't said before the range you said before column I.

Then column D is before column I.

Anyway, the OP problem was solved.

Thank you for your time.

Markmzz

T. Valko · Sep 20, 2011

markmzz said:
Sorry, but if I insert a column between your range (A1:I3) we have the same problem.

Look at this examples:

"If they insert new rows/columns on the sheet that holds the formula then that can cause those formulas to fail."

Excel 2007<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><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><TH>L</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"></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="FONT-WEIGHT: bold; TEXT-ALIGN: center">C</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</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: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col02</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col03</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,220</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,006</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,351</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,059</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">779</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">938</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,224</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,467</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,267</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">257</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,223</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">776</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">970</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,453</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">928</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,316</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">294</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,579</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,256</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,084</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,667</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,745</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,011</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">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: center"></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">12</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: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,220</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,006</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,351</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,059</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">779</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">938</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,224</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,467</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,267</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">540</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">257</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,223</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">17</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">776</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">970</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,453</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">18</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">928</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,316</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">294</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">19</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,579</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,256</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,084</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">20</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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,667</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,745</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,011</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR></TBODY></TABLE>Master-Before

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<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; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>I3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(A$1:I3),COLUMNS($A3:I3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(B$1:J3),COLUMNS($A3:J3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>K3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(C$1:K3),COLUMNS($A3:K3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>I13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(I3),COLUMN(I3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(J3),COLUMN(J3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>K13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(K3),COLUMN(K3)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Workbook Defined Names<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; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>CPool</TH><TD style="TEXT-ALIGN: left">={"Sheet001","Sheet002","Sheet003"}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
The sheet after a new column D.

Excel 2007

<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><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><TH>L</TH><TH>M</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"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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="FONT-WEIGHT: bold; TEXT-ALIGN: center">C</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col02</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Col03</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,006</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,351</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">779</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">938</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,467</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,267</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">257</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,223</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">970</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,453</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,316</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">294</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,256</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,084</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,745</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">1,011</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #ffff00">T. Valko Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></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">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,006</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,351</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">779</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">938</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,467</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,267</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">257</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,223</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">970</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,453</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,316</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">294</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,256</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,084</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ff0000; 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: center"></TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,745</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">1,011</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: center">0</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Markmzz Formula</TD></TR></TBODY></TABLE>Master-After

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<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; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(A$1:J3),COLUMNS($A3:J3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>K3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(B$1:K3),COLUMNS($A3:K3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>L3</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROWS(C$1:L3),COLUMNS($A3:L3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>J13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(J3),COLUMN(J3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>K13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(K3),COLUMN(K3)))))</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>L13</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!"&ADDRESS(ROW(L3),COLUMN(L3)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Workbook Defined Names<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; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>CPool</TH><TD style="TEXT-ALIGN: left">={"Sheet001","Sheet002","Sheet003"}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Look at values of the Col03. Where I'm wrong?

Markmzz

Ok, now I see what you mean.

Yes, that could cause a problem.

Sharon in Denver · Sep 21, 2011

Gentlemen, thank you for this in-depth evaluation! I don't, as a matter of course, insert columns in these file, but it will be good to know that I can't with this formula!

You both have taught me a lot on this one, and I appreciate your generosity of knowledge!

-Sharon

T. Valko · Sep 21, 2011

Sharon in Denver said:
Gentlemen, thank you for this in-depth evaluation! I don't, as a matter of course, insert columns in these file, but it will be good to know that I can't with this formula!

You both have taught me a lot on this one, and I appreciate your generosity of knowledge!

-Sharon

This is one of the disadvantages of using INDIRECT that most folks overlook.

Thanks for the feedback!

Sum across multiple tabs, criteria on each tab

Sharon in Denver

Board Regular

Sharon in Denver

Board Regular

Excel Facts

markmzz

MrExcel MVP

markmzz

MrExcel MVP

T. Valko

Well-known Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

T. Valko

Well-known Member

Sharon in Denver

Board Regular

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

Sum across multiple tabs, criteria on each tab

Board Regular

Board Regular

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock