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!

 

[T. Valko]

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!

So, basically what you want to do is:

SUMIF(sheetnameH1,"A",sheetnameI4)

SUMIF(sheetnameH1,"B",sheetnameI4)

You could do this with a SUMPRODUCT(SUMIF but you'd have to list the sheet names in a range of cells. If the sheet names follow some kind of sequential naming pattern then you might be able to avoid the range list.

Are the sheet names random names or do they follow some pattern?

 

[Sharon in Denver]

For all intents and purposes, the sheet names are random. And they are long. They are oil well names in this format: 100.11-22-333-44W4.0
So, while there is a pattern that a reader understands, I don't think it's simple enough to formulaicly recognize.

Your assumption is correct:
Sum I4 all sheets where H1 = A
Sum I4 all sheets where H1 = B

 

[T. Valko]

For all intents and purposes, the sheet names are random. And they are long. They are oil well names in this format: 100.11-22-333-44W4.0
So, while there is a pattern that a reader understands, I don't think it's simple enough to formulaicly recognize.

Your assumption is correct:
Sum I4 all sheets where H1 = A
Sum I4 all sheets where H1 = B

Ok, then you'd have to list the sheet names in a range of cells.

Let's assume the sheet names are in the range A1:A100

B1 = A or B

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A100&"'!H1"),B1,INDIRECT("'"&A1:A100&"'!I4")))

It's not very cool to have to list all those sheet names. You might be better off putting a formula on each sheet in the same cell. Like this:

On each sheet in cell Z1:

=(H1="A")*I4

Then, you can sum that cell across all the sheets like this:

=SUM(First:Last!Z1)

See this. It might be of help in listing the sheet names if you go that route.

http://www.mrexcel.com/forum/showpost.php?p=2118912&postcount=1

 

[Sharon in Denver]

If I am understanding, that list would have to be on the same sheet as the summary data correct? Could it be referenced on a separate tab? Because I do have that. All the A tab names on one tab, all the B's on a separate tab.

 

[T. Valko]

If I am understanding, that list would have to be on the same sheet as the summary data correct? Could it be referenced on a separate tab? Because I do have that. All the A tab names on one tab, all the B's on a separate tab.

The list of sheet names can be on any sheet. Just include the sheet name in the formula if the list is on a different sheet than the formula.

Or, better yet, give the list of sheet names a defined named range.

Name: Sheets
Refers to: =Sheet1!$A$1:$A$100

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!H1"),B1,INDIRECT("'"&Sheets&"'!I4")))

 

[Sharon in Denver]

Thank you so much Biff! I will give that a try and let you know how it turns out.

-S

 

[markmzz]

Note: use this code with a copy of your workbook.

Try this small code to created the list of sheets names in the column X: in the Master sheet:

Sub mySheetNames()
    Dim i, j As Integer
    Worksheets("[COLOR=red]Master[/COLOR]").Select
    Range("[COLOR=red]X[/COLOR]:[COLOR=red]X[/COLOR]").ClearContents
    Range("[COLOR=red]X[/COLOR]1").Value = "Sheets Names"
    j = 2
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name <> "[COLOR=red]Master[/COLOR]" Then
            Range("[COLOR=red]X[/COLOR]" & j).Value = Worksheets(i).Name
            j = j + 1
        End If
    Next i
End Sub

Copy the code above.

Rigth click in the tab of one sheet (the Master sheet for example) and choose View code.

In the VBA windows that displayed, open Insert menu and choose Module.

Paste the code copied before into VBA windows and close the VBA window.

Now, press Alt+F8<F8>, choose mySheetNames and click in Run.

<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 /><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><th>V</th><th>W</th><th>X</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><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Sheets Names</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><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style=";">Sheet001</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><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style=";">Sheet002</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,033</td><td style="text-align: center;;">1,096</td><td style="text-align: center;;">1,578</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">324</td><td style="text-align: center;;">491</td><td style="text-align: center;;">1,391</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">709</td><td style="text-align: center;;">605</td><td style="text-align: center;;">187</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style=";">Sheet003</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">964</td><td style="text-align: center;;">1,621</td><td style="text-align: center;;">1,229</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">660</td><td style="text-align: center;;">1,436</td><td style="text-align: center;;">653</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">304</td><td style="text-align: center;;">185</td><td style="text-align: center;;">576</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,316</td><td style="text-align: center;;">1,924</td><td style="text-align: center;;">1,359</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,040</td><td style="text-align: center;;">1,144</td><td style="text-align: center;;">1,199</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">276</td><td style="text-align: center;;">780</td><td style="text-align: center;;">160</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,305</td><td style="text-align: center;;">1,662</td><td style="text-align: center;;">1,289</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">645</td><td style="text-align: center;;">1,342</td><td style="text-align: center;;">507</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">660</td><td style="text-align: center;;">320</td><td style="text-align: center;;">782</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;">994</td><td style="text-align: center;;">1,630</td><td style="text-align: center;;">1,282</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">855</td><td style="text-align: center;;">737</td><td style="text-align: center;;">690</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">139</td><td style="text-align: center;;">893</td><td style="text-align: center;;">592</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,398</td><td style="text-align: center;;">800</td><td style="text-align: center;;">1,185</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,085</td><td style="text-align: center;;">512</td><td style="text-align: center;;">574</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">313</td><td style="text-align: center;;">288</td><td style="text-align: center;;">611</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,374</td><td style="text-align: center;;">1,167</td><td style="text-align: center;;">977</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,144</td><td style="text-align: center;;">676</td><td style="text-align: center;;">566</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">230</td><td style="text-align: center;;">491</td><td style="text-align: center;;">411</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;">955</td><td style="text-align: center;;">1,766</td><td style="text-align: center;;">1,610</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">596</td><td style="text-align: center;;">1,416</td><td style="text-align: center;;">1,504</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">359</td><td style="text-align: center;;">350</td><td style="text-align: center;;">106</td><td style="text-align: center;;"></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: center;;"></td><td style="text-align: center;;">1,804</td><td style="text-align: center;;">1,237</td><td style="text-align: center;;">647</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,248</td><td style="text-align: center;;">1,107</td><td style="text-align: center;;">469</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">556</td><td style="text-align: center;;">130</td><td style="text-align: center;;">178</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,430</td><td style="text-align: center;;">1,390</td><td style="text-align: center;;">1,988</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,205</td><td style="text-align: center;;">1,047</td><td style="text-align: center;;">1,484</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">225</td><td style="text-align: center;;">343</td><td style="text-align: center;;">504</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,499</td><td style="text-align: center;;">1,949</td><td style="text-align: center;;">1,728</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,156</td><td style="text-align: center;;">1,496</td><td style="text-align: center;;">1,320</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">343</td><td style="text-align: center;;">453</td><td style="text-align: center;;">408</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;">892</td><td style="text-align: center;;">1,832</td><td style="text-align: center;;">1,925</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">719</td><td style="text-align: center;;">1,308</td><td style="text-align: center;;">1,223</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">173</td><td style="text-align: center;;">524</td><td style="text-align: center;;">702</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;">776</td><td style="text-align: center;;">1,897</td><td style="text-align: center;;">1,882</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">588</td><td style="text-align: center;;">1,366</td><td style="text-align: center;;">1,201</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">188</td><td style="text-align: center;;">531</td><td style="text-align: center;;">681</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,622</td><td style="text-align: center;;">2,436</td><td style="text-align: center;;">1,209</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">990</td><td style="text-align: center;;">1,717</td><td style="text-align: center;;">661</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">632</td><td style="text-align: center;;">719</td><td style="text-align: center;;">548</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,437</td><td style="text-align: center;;">1,418</td><td style="text-align: center;;">1,800</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,080</td><td style="text-align: center;;">1,100</td><td style="text-align: center;;">1,587</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">357</td><td style="text-align: center;;">318</td><td style="text-align: center;;">213</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,277</td><td style="text-align: center;;">1,292</td><td style="text-align: center;;">1,542</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">767</td><td style="text-align: center;;">1,107</td><td style="text-align: center;;">1,106</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">510</td><td style="text-align: center;;">185</td><td style="text-align: center;;">436</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,731</td><td style="text-align: center;;">1,504</td><td style="text-align: center;;">2,157</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">949</td><td style="text-align: center;;">1,209</td><td style="text-align: center;;">1,485</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">782</td><td style="text-align: center;;">295</td><td style="text-align: center;;">672</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;">1,847</td><td style="text-align: center;;">1,638</td><td style="text-align: center;;">2,107</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1,207</td><td style="text-align: center;;">1,234</td><td style="text-align: center;;">1,441</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">640</td><td style="text-align: center;;">404</td><td style="text-align: center;;">666</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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><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 />
Markmzz

 

[markmzz]

Note: use this code with a copy of your workbook.

Another code that created the name STNames.

Sub mySheetNames2()
    Dim i, j As Integer
    Dim myArray() As String
    ReDim myArray(1 To Worksheets.Count - 1)
    j = 1
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name <> "Master" Then
            myArray(j) = Worksheets(i).Name
            j = j + 1
        End If
    Next i
    Names.Add Name:="STNames", RefersTo:=myArray
End Sub

Markmzz

 

[Sharon in Denver]

Thanks Mark! I have a macro that creates a list of all the tab names. I'm still trying to get the Sumproduct(sumif formula to work. Will report back soon!