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]

Here's what I have, and I'm getting an error. Can you see what I've done wrong?

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

I've named the range with my two lists BPool and CPool. They refer to the tabs with the list of all the wells in each pool.

 

[T. Valko]

Here's what I have, and I'm getting an error. Can you see what I've done wrong?

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

I've named the range with my two lists BPool and CPool. They refer to the tabs with the list of all the wells in each pool.

What is "C" supposed to be? Is it supposed to be a cell reference like maybe C1?

 

[Sharon in Denver]

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")))

<<<<<<<<<<<<<<<<<I don't know how to insert that as quoted text>>>>>>>>>>>>>>>>

The 'C' is the value in H1 that differentiates the B-Pool from the C-Pool. So, I have two tabs with a list of each pool. In the cell H1 I have B on the tab that has the B-Pool list and C on the tab that has the C-Pool list.

 

[T. Valko]

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")))

<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>

The 'C' is the value in H1 that differentiates the B-Pool from the C-Pool. So, I have two tabs with a list of each pool. In the cell H1 I have B on the tab that has the B-Pool list and C on the tab that has the C-Pool list.

If "C" is the criteria then it needs to be quoted if you're hardcoding it directly into the formula:

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

 

[Sharon in Denver]

That's exactly what I have. I was trying to quote your previous message, but I don't know how to do that, and the <<<'s took out my note to that effect.

This is what I have:

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

 

[Sharon in Denver]

OOPS!!! I just noticed the quotes around the C!! But that give me me a #REF! error. Here's what I have now:

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

 

[T. Valko]

OOPS!!! I just noticed the quotes around the C!! But that give me me a #REF! error. Here's what I have now:

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

I'll be away for a few hours.

I'll put together a sample file when I get back.

 

[NBVC]

Note:

All the sheetnames in CPool must exactly match the actual sheetnames in your workbooks.

 

[Sharon in Denver]

NVBC, the sheet names match exactly. I have a macro that creates a list of all the sheet names, and I just parsed out the B-Pool and C-Pool.

Biff, I am leaving at noon, so we may have to tackle this next week! Have a great weekend!

 

[NBVC]

I did a quick test and it worked fine for me with your exact formula, using a couple of sheets in CPool...

You don't have any blank cells in the CPool range, do you?