Solved: Is there a easier way

[rama4672]

I use this formula to calculate each sheet and range, it has to calculate 52 sheets (Week 1 to Week 52).

=SUMIF(Sheet1!A1:A56,A1,Sheet1!B1:B56)+SUMIF(Sheet2!A1:A56,A1,Sheet2!B1:B56 )
This is only for 2 Sheets,Now to do that for 52 sheets the formula would be so long, Is there a easier way to do it
I have tried =SUMIF(Sheet1!A1:A56:Sheet2!A1:A34,A1,Sheet1!B1:B56:Sheet2!B1:B56)
But I get a value error

TIA

Ian

[Jacob Hilderbrand]

You can do a 3D reference with Sum. What I would do is designate a cell on each worksheet to do the SumIf. Each cell would then have the total for that sheet that you want to sum up. Then use a formula like this.

=SUM(Sheet1:Sheet52!C1)

[Jacob Hilderbrand]

If you do not want to put the SumIf on each worksheet then there is another method you can use.

On your summary sheet try this.

In A1 put this.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$C$1:$C$3&"'!A1:A100"),B1,INDIRECT("'"&$C$1:$C$3&"'!B1:B100")))

In B1 put the criteria you are looking for.

In C1:C3 put this.

=Sheet1
=Sheet2
=Sheet3

If this works for you then in Column C list all 52 sheets and change the range in the formula to get all the sheet names. In this formula we are checking Column A on each worksheet for a match to B1 on the summary worksheet. And if there is a match, get the value from Column B.

Change the ranges as needed, but you cannot use full column references. For example: A:A will error out. A2:A65536 or A1:A65535 will be fine.

[Bob Phillips]

If you do not want to put the SumIf on each worksheet then there is another method you can use.

On your summary sheet try this.

In A1 put this.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$C$1:$C$3&"'!A1:A100"),B1,INDIRECT("'"&$C$1:$C$3&"'!B1:B100")))

In B1 put the criteria you are looking for.

In C1:C3 put this.

=Sheet1
=Sheet2
=Sheet3

If this works for you then in Column C list all 52 sheets and change the range in the formula to get all the sheet names. In this formula we are checking Column A on each worksheet for a match to B1 on the summary worksheet. And if there is a match, get the value from Column B.

Change the ranges as needed, but you cannot use full column references. For example: A:A will error out. A2:A65536 or A1:A65535 will be fine.

If the sheets are structured as shown, Sheet1, Sheet2, etc, you don't need a list of sheetnames, just

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:52"))&"!A1:A56"),A1,
INDIRECT("Sheet"&ROW(INDIRECT("1:52"))&"!B1:B56")))

.

[rama4672]

Thanks XLD and DRJ
I have tried what you suggested XLD, but i get a #Ref! error

I Have attached a short version of what i am trying to do



Regards

Ian

[Bob Phillips]

Thanks XLD and DRJ
I have tried what you suggested XLD, but i get a #Ref! error

I Have attached a short version of what i am trying to do

You only have two sheets, so you have to change 1:52 to 1:2

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:2"))&"!A1:A56"),A1,
INDIRECT("Sheet"&ROW(INDIRECT("1:2"))&"!B1:B56")))

You will need to change it back when you get 52 sheets.

[rama4672]

Excellent
Thanks XLD that works great

Regards


Ian