Sum across multiple tabs, criteria on each tab

[Sharon in Denver]

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]

That was it! Blanks in the Named Range!

MUAH MUAH MUAH!! Thank you SOO much! All of you! For your help. This one was making me crazy on a bad week. Y'all made me smile!

Happy Friday!!!

 

[Sharon in Denver]

Although the cell reference doesn't change when I drag it down. Grrrr

 

[NBVC]

You can play with that too...

for example if you want to change the I4 to I5, etc as you copy down

=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!I"&ROWS($A$1:$A4))))

starts at I4 and copying down changes to I5, etc...

and you can do the same for the H range if you want.

 

[Sharon in Denver]

I'm not getting that to work right now, but I have to head out soon, so I'm going to table it until next week. You guys are great!

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><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>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">All</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="font-weight: bold;text-align: center;;">A</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="font-weight: bold;text-align: center;;">B</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</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: center;;"></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: center;;"></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></tr><tr ><td style="color: #161120;text-align: center;">3</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: center;;"></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: center;;"></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></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,529</td><td style="text-align: center;;">1,295</td><td style="text-align: center;;">1,118</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">673</td><td style="text-align: center;;">314</td><td style="text-align: center;;">261</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">856</td><td style="text-align: center;;">981</td><td style="text-align: center;;">857</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,148</td><td style="text-align: center;;">1,048</td><td style="text-align: center;;">1,801</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">726</td><td style="text-align: center;;">342</td><td style="text-align: center;;">664</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">422</td><td style="text-align: center;;">706</td><td style="text-align: center;;">1,137</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,475</td><td style="text-align: center;;">1,009</td><td style="text-align: center;;">1,546</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">204</td><td style="text-align: center;;">137</td><td style="text-align: center;;">152</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,271</td><td style="text-align: center;;">872</td><td style="text-align: center;;">1,394</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,659</td><td style="text-align: center;;">1,650</td><td style="text-align: center;;">1,252</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">773</td><td style="text-align: center;;">284</td><td style="text-align: center;;">221</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">886</td><td style="text-align: center;;">1,366</td><td style="text-align: center;;">1,031</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,578</td><td style="text-align: center;;">1,483</td><td style="text-align: center;;">1,555</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">799</td><td style="text-align: center;;">164</td><td style="text-align: center;;">430</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">779</td><td style="text-align: center;;">1,319</td><td style="text-align: center;;">1,125</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,249</td><td style="text-align: center;;">1,470</td><td style="text-align: center;;">685</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">533</td><td style="text-align: center;;">510</td><td style="text-align: center;;">367</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">716</td><td style="text-align: center;;">960</td><td style="text-align: center;;">318</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,442</td><td style="text-align: center;;">1,667</td><td style="text-align: center;;">1,049</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">242</td><td style="text-align: center;;">633</td><td style="text-align: center;;">362</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,200</td><td style="text-align: center;;">1,034</td><td style="text-align: center;;">687</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,627</td><td style="text-align: center;;">1,857</td><td style="text-align: center;;">1,616</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">128</td><td style="text-align: center;;">500</td><td style="text-align: center;;">644</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,499</td><td style="text-align: center;;">1,357</td><td style="text-align: center;;">972</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,579</td><td style="text-align: center;;">1,642</td><td style="text-align: center;;">637</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">479</td><td style="text-align: center;;">708</td><td style="text-align: center;;">305</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,100</td><td style="text-align: center;;">934</td><td style="text-align: center;;">332</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;">2,025</td><td style="text-align: center;;">886</td><td style="text-align: center;;">1,221</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">786</td><td style="text-align: center;;">104</td><td style="text-align: center;;">804</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,239</td><td style="text-align: center;;">782</td><td style="text-align: center;;">417</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,494</td><td style="text-align: center;;">1,601</td><td style="text-align: center;;">1,864</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">628</td><td style="text-align: center;;">849</td><td style="text-align: center;;">893</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">866</td><td style="text-align: center;;">752</td><td style="text-align: center;;">971</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,791</td><td style="text-align: center;;">805</td><td style="text-align: center;;">1,807</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">322</td><td style="text-align: center;;">340</td><td style="text-align: center;;">600</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,469</td><td style="text-align: center;;">465</td><td style="text-align: center;;">1,207</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,031</td><td style="text-align: center;;">1,011</td><td style="text-align: center;;">1,065</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">287</td><td style="text-align: center;;">330</td><td style="text-align: center;;">199</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">744</td><td style="text-align: center;;">681</td><td style="text-align: center;;">866</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,653</td><td style="text-align: center;;">1,032</td><td style="text-align: center;;">1,915</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">612</td><td style="text-align: center;;">145</td><td style="text-align: center;;">677</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,041</td><td style="text-align: center;;">887</td><td style="text-align: center;;">1,238</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;"></td><td style="text-align: center;;">877</td><td style="text-align: center;;">1,672</td><td style="text-align: center;;">1,127</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">406</td><td style="text-align: center;;">542</td><td style="text-align: center;;">112</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">471</td><td style="text-align: center;;">1,130</td><td style="text-align: center;;">1,015</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;"></td><td style="text-align: center;;">937</td><td style="text-align: center;;">1,884</td><td style="text-align: center;;">1,789</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">103</td><td style="text-align: center;;">883</td><td style="text-align: center;;">827</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">834</td><td style="text-align: center;;">1,001</td><td style="text-align: center;;">962</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,781</td><td style="text-align: center;;">1,536</td><td style="text-align: center;;">2,008</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">680</td><td style="text-align: center;;">596</td><td style="text-align: center;;">635</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,101</td><td style="text-align: center;;">940</td><td style="text-align: center;;">1,373</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,668</td><td style="text-align: center;;">2,100</td><td style="text-align: center;;">1,256</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">699</td><td style="text-align: center;;">755</td><td style="text-align: center;;">355</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">969</td><td style="text-align: center;;">1,345</td><td style="text-align: center;;">901</td></tr><tr ><td style="color: #161120;text-align: center;">22</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: center;;"></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: center;;"></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></tr><tr ><td style="color: #161120;text-align: center;">23</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: center;;">*****</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: center;;">*****</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></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Master</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">=SUM(<font color="Blue">Sheet001:Sheet003!I4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J4</th><td style="text-align:left">=SUM(<font color="Blue">Sheet001:Sheet003!J4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">=SUM(<font color="Blue">Sheet001:Sheet003!K4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&STNames&"'!H1"</font>),$M$1,INDIRECT(<font color="Green">"'"&STNames&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">I4</font>),COLUMN(<font color="Teal">I4</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&STNames&"'!H1"</font>),$M$1,INDIRECT(<font color="Green">"'"&STNames&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">J4</font>),COLUMN(<font color="Teal">J4</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&STNames&"'!H1"</font>),$M$1,INDIRECT(<font color="Green">"'"&STNames&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">K4</font>),COLUMN(<font color="Teal">K4</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&STNames&"'!H1"</font>),$R$1,INDIRECT(<font color="Green">"'"&STNames&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">I4</font>),COLUMN(<font color="Teal">I4</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&STNames&"'!H1"</font>),$R$1,INDIRECT(<font color="Green">"'"&STNames&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">J4</font>),COLUMN(<font color="Teal">J4</font>)</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&STNames&"'!H1"</font>),$R$1,INDIRECT(<font color="Green">"'"&STNames&"'!"&ADDRESS(<font color="Purple">ROW(<font color="Teal">K4</font>),COLUMN(<font color="Teal">K4</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">STNames</th><td style="text-align:left">={"Sheet001","Sheet002","Sheet003"}</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[T. Valko]

Although the cell reference doesn't change when I drag it down. Grrrr

Well, at least we made some progress!

Which cell reference do you want to change when you drag copy the formula down a column?

What is the cell address of the very first cell you're entering the formula in?

 

[Sharon in Denver]

The -I- column reference is what should increment. It should also move accordingly if I copy it over to K or any other column. H1 I would absolute, since that is the criteria, but I'm summing info from all the other tabs and there are several columns. The formula stars at I3 and goes down to I558. But the data gets updated regularly (each row represents a month) so I need it to copy down and across and adjust as necessary.

Sorry to be such a bother. Hope y'all had a good weekend!

 

[T. Valko]

The -I- column reference is what should increment. It should also move accordingly if I copy it over to K or any other column. H1 I would absolute, since that is the criteria, but I'm summing info from all the other tabs and there are several columns. The formula stars at I3 and goes down to I558. But the data gets updated regularly (each row represents a month) so I need it to copy down and across and adjust as necessary.

Sorry to be such a bother. Hope y'all had a good weekend!

Try this...

Entered in cell I3 and copied down and/or across as needed:

=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDEX(INDIRECT("'"&CPool&"'!I4:Z100"),ROWS(I$3:I3),COLUMNS($I3:I3))))

Adjust the range I4:Z100 to suit.

 

[Sharon in Denver]

Biff, this returns FALSE. The I4:Z100 reference, what is that?

 

[T. Valko]

Biff, this returns FALSE. The I4:Z100 reference, what is that?

This is the "base" formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDIRECT("'"&CPool&"'!I4")))

That will sum cell I4 on the sheets in the CPool group where cell H1 = C.

So that you can drag copy the formula across a row or down a column so that the I4 cell references increments accordingly I used this expression:

INDEX(INDIRECT("'"&CPool&"'!I4:Z100"),ROWS(I$3:I3),COLUMNS($I3:I3))

I4:Z100 is the ENTIRE range of cells to be summed. Adjust that to reflect your actual ENTIRE range of cells to be summed.

As you drag copy this formula down a column:

=SUMPRODUCT(SUMIF(INDIRECT("'"&CPool&"'!H1"),"C",INDEX(INDIRECT("'"&CPool&"'!I4:Z100"),ROWS(I$3:I3),COLUMNS($I3:I3))))

The cell reference to I4 will increment like this:

SUMIF(H1,"C",I4)
SUMIF(H1,"C",I5)
SUMIF(H1,"C",I6)
SUMIF(H1,"C",I7)
SUMIF(H1,"C",I8)
etc
etc

As you drag copy across a row the cell reference to I4 will increment like this:

SUMIF(H1,"C",I4)
SUMIF(H1,"C",J4)
SUMIF(H1,"C",K4)
SUMIF(H1,"C",L4)
SUMIF(H1,"C",M4)
etc
etc

Since I don't know your actual end of range I just randomly chose to use Z100.

Adjust this range to suit your needs.