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]

The smallest I can get this file is 10,000 kb. Can I send it to you directly?

-Sharon

 

[T. Valko]

The smallest I can get this file is 10,000 kb. Can I send it to you directly?

-Sharon

Meh, that's kind of big.

If the smallest you can make your file is ~10mb then what you're trying to do may not be very efficient. It sounds like you need/want a whole lot of these formulas and because they use volatile functions this may affect the file performance (slow to react to data entry and slow recalculation).

 

[Sharon in Denver]

It is a big file. There are a lot of formulas in it and there are just a lot of data.

Thanks so much for trying, I do really appreciate it, and I've learned a bit more about sumif(sumproduct!

 

[Sharon in Denver]

I've taken out about 90% of the data so it's only 1MB now. It's super easy to look at and work with for the purposes of the formula. Would you mind looking at it?

 

[T. Valko]

It is a big file. There are a lot of formulas in it and there are just a lot of data.

Thanks so much for trying, I do really appreciate it, and I've learned a bit more about sumif(sumproduct!

Let's not give up!

Let's try something different.

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

The cell reference to I4 will increment like this as you copy down a column:

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

The cell reference to I4 will increment like this as you copy across a row:

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

 

[Sharon in Denver]

Thanks Biff, this one is killing me! I tried that formula, and it returns TRUE but no value.

BTW, I have the file down to 450 kb if you want to see it. It's mostly just data that makes it so big. So, instead of 60 years of data on 150 wells, I have 6 wells and 6 months of data. Makes it a whole lot easier!

 

[T. Valko]

Thanks Biff, this one is killing me! I tried that formula, and it returns TRUE but no value.

BTW, I have the file down to 450 kb if you want to see it. It's mostly just data that makes it so big. So, instead of 60 years of data on 150 wells, I have 6 wells and 6 months of data. Makes it a whole lot easier!

I don't see how that formula can return TRUE.

OK, I'll send you a private message.

 

[markmzz]

Try this:

Note: look at your email too.

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></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></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">2</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-WEIGHT: bold">
Prod Date


</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-WEIGHT: bold">Monthly
Oil
(bbl)


</TD><TD style="TEXT-ALIGN: center; COLOR: #0070c0; FONT-WEIGHT: bold">Cum Prd
Oil
(bbl)


</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-WEIGHT: bold">Monthly
Water
(bbl)


</TD><TD style="TEXT-ALIGN: center; COLOR: #0070c0; FONT-WEIGHT: bold">Cum Prd
Water
(bbl)


</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">Jan-1965</TD><TD style="TEXT-ALIGN: right">10.00 </TD><TD style="TEXT-ALIGN: right">10.00 </TD><TD style="TEXT-ALIGN: right">13.00 </TD><TD style="TEXT-ALIGN: right">13.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">Feb-1965</TD><TD style="TEXT-ALIGN: right">11.00 </TD><TD style="TEXT-ALIGN: right">21.00 </TD><TD style="TEXT-ALIGN: right">12.80 </TD><TD style="TEXT-ALIGN: right">25.80 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">Mar-1965</TD><TD style="TEXT-ALIGN: right">7.50 </TD><TD style="TEXT-ALIGN: right">28.50 </TD><TD style="TEXT-ALIGN: right">9.20 </TD><TD style="TEXT-ALIGN: right">35.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">Apr-1965</TD><TD style="TEXT-ALIGN: right">7.75 </TD><TD style="TEXT-ALIGN: right">36.25 </TD><TD style="TEXT-ALIGN: right">10.15 </TD><TD style="TEXT-ALIGN: right">45.15 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">May-1965</TD><TD style="TEXT-ALIGN: right">11.00 </TD><TD style="TEXT-ALIGN: right">47.25 </TD><TD style="TEXT-ALIGN: right">16.00 </TD><TD style="TEXT-ALIGN: right">61.15 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">Jun-1965</TD><TD style="TEXT-ALIGN: right">11.10 </TD><TD style="TEXT-ALIGN: right">58.35 </TD><TD style="TEXT-ALIGN: right">13.60 </TD><TD style="TEXT-ALIGN: right">74.75 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">Jul-1965</TD><TD style="TEXT-ALIGN: right">11.80 </TD><TD style="TEXT-ALIGN: right">70.15 </TD><TD style="TEXT-ALIGN: right">15.30 </TD><TD style="TEXT-ALIGN: right">90.05 </TD></TR></TBODY></TABLE>B-Pool TOTAL


<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(SUBTOTAL(9,INDIRECT("'"&BPool&"'!"&ADDRESS(ROW('100.06-13-006-17W4.0'!I3),COLUMN('100.06-13-006-17W4.0'!I3)))))</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>BPool</TH><TD style="TEXT-ALIGN: left">='B-Pool List'!$A$1:$A$3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[Sharon in Denver]

This seems to work Mark! Biff, so you does yours. I'll test both on the big file to see if either runs faster or if there are any issues.

Thank you both SO much!! For those following this mini drama, the formula Biff gave me was the one above, it just took three times of pasting the exact same thing. Gremlins in the Machine?

Mark's Formula:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'"&CPool&"'!"&ADDRESS(ROW('100.06-13-006-17W4.0'!I3),COLUMN('100.06-13-006-17W4.0'!I3)))))

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

Very similar!

 

[T. Valko]

This seems to work Mark! Biff, so you does yours. I'll test both on the big file to see if either runs faster or if there are any issues.

Thank you both SO much!! For those following this mini drama, the formula Biff gave me was the one above, it just took three times of pasting the exact same thing. Gremlins in the Machine?

Mark's Formula:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'"&CPool&"'!"&ADDRESS(ROW('100.06-13-006-17W4.0'!I3),COLUMN('100.06-13-006-17W4.0'!I3)))))

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

Very similar!

The OP contacted me via email and the problem has been solved using this formula:

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

Good deal!

I was beginning to think we may not solve this one. I hate when that happens.

Good luck and thanks for the feedback!