SUMIF across multiple sheets?

[Wrathor]

Is it possible to do a sumif across multiple sheets?

I have 212 sheets with data in A3:G90.
The values in A3:A90 always start with year 14 and count up from there.
The values in B3:B90 start anywhere from age 49 to age 80, and count up until they reach age 99 then stop.

On a Composite sheet, I'm trying to sum the values in columns G when 99 falls under the year in column A.

The formula I tried is: =SUMIF('Sheet1:Sheet212'!B3,99,'Sheet1:Sheet212'!G3)

This formula is returning a value error.

Any suggestions?

 

[pgc01]

Hi

SumIf() does not accept a 3d range.

You have to either use code or write down the worksheets names and SumIf() each one.

Ex. with a formula.

Write the worksheets names in Z1:Z212

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z212&"'!B3"),99,INDIRECT("'"&Z1:Z212&"'!G3")))

 

[Wrathor]

This is working great for the formula in B3 of my composite sheet!

Is there a way to make the B3 and G3 in the INDIRECT() change if I want to drag down to formula; rather than changing it in each cell?

 

[pgc01]

In the first cell where you use the formula, try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$Z$1:$Z$212&"'!B"&ROW(B3)),99,INDIRECT("'"&$Z$1:$Z$212&"'!G"&ROW(G3))))

Now when you drag down Row(B3) will change and generates the sequence you want.

 

[Wrathor]

Thanks a million! This is exactly what I needed!

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[NLBeer]

Hi,

I tried using the formula above but i get ref# issues.
I have one comp sheet where the info should go.
I have ranged all the sheets names as 'sheetlist' in cell Z5:Z12
On every sheet the info needed are in cell K6:K21
the criteria is in cell U9 (sheet name).

When i input a number, say; 1, in cell U9 i want it to auto take the values in sheet1 which is named as 1, sheet 2 is named 2 etc up to 12.

can you please help?

 

[manishc1989]

In the first cell where you use the formula, try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$Z$1:$Z$212&"'!B"&ROW(B3)),99,INDIRECT("'"&$Z$1:$Z$212&"'!G"&ROW(G3))))

Now when you drag down Row(B3) will change and generates the sequence you want.

I'm looking for the same solution. However my sheets counts are dynamic. Is it possible to use rows formula in $Z$212. So when I paste the new sheets names then the formula get updated automatically.

I've tried several times but unable to succeed.

 

[pgc01]

Hi

You can use a dynamic named range that adjusts to the number of worksheets in column Z

For ex., assuming the worksheets names
- starting in Z1
- going down
- in contiguous cells
- nothing else in Z:Z

you could use the name:

Name: WorksheetList
Refers to: =OFFSET(Sheet2!$Z$1;0;0;COUNTA(Sheet2!$Z:$Z);1)

Remark: the formula is not robust. If you expect to insert/delete columns/rows use another formulation, for ex. with Indirect()

 

[manishc1989]

Hi

You can use a dynamic named range that adjusts to the number of worksheets in column Z

For ex., assuming the worksheets names
- starting in Z1
- going down
- in contiguous cells
- nothing else in Z:Z

you could use the name:

Name: WorksheetList
Refers to: =OFFSET(Sheet2!$Z$1;0;0;COUNTA(Sheet2!$Z:$Z);1)

Remark: the formula is not robust. If you expect to insert/delete columns/rows use another formulation, for ex. with Indirect()

After spending some time on the final formula to incorporate the worksheetlist named range. It finally worked!!

Thank you very much for your help! Really appreciated your presence.
Here is the formula:-
=SUMPRODUCT(COUNTIFS(INDIRECT(("'"&WorksheetList&"'!J6:S6")),'ListOfWorksheetsnames'!G$5))

In the above formula Worksheet list is the named range in the ListOfWorksheetsnames worksheet.

Would you please help me to understand how this formula worked?