Sumif for multiple sheets within a workbook

[Claymationator]

I tried searching the forums for an answer to this, but was unable to find anything.

I have multiple sheets in my workbook and each one will have the data for one week. I have this for an entire quarter (13 weeks) and then I have a summary sheet that I want to add up each one of the weeks within the sheet. Is it possible to do a SUMIF for multiple sheets? I tried this formula and it only returned a #VALUE! error.

=SUMIF(Sheet1:Sheet13!G98:G217,'Q3 Overview Breakdown'!G98,Sheet1:Sheet13!H98:H217)

I was able to get the totals to add up, but I did a sumif for each sheet and then added all 13 together, which makes things a lot more messy. The formula that I have now is below, but I can't just drag the formula and have it copy for all of the other totals that I need to add.

=SUMIF(Sheet1!$G$98:$G$217,$G$98,Sheet1!$H$98:$H$217)+SUMIF(Sheet2!$G$98:$G$217,$G$98,Sheet2!$H$98:$H$217)+SUMIF(Sheet3!$G$98:$G$217,$G$98,Sheet3!$H$98:$H$217)+SUMIF(Sheet4!$G$98:$G$217,$G$98,Sheet4!$H$98:$H$217)+SUMIF(Sheet5!$G$98:$G$217,$G$98,Sheet5!$H$98:$H$217)+SUMIF(Sheet6!$G$98:$G$217,$G$98,Sheet6!$H$98:$H$217)+SUMIF(Sheet7!$G$98:$G$217,$G$98,Sheet7!$H$98:$H$217)+SUMIF(Sheet8!$G$98:$G$217,$G$98,Sheet8!$H$98:$H$217)+SUMIF(Sheet9!$G$98:$G$217,$G$98,Sheet9!$H$98:$H$217)+SUMIF(Sheet10!$G$98:$G$217,$G$98,Sheet10!$H$98:$H$217)+SUMIF(Sheet11!$G$98:$G$217,$G$98,Sheet11!$H$98:$H$217)+SUMIF(Sheet12!$G$98:$G$217,$G$98,Sheet12!$H$98:$H$217)+SUMIF(Sheet13!$G$98:$G$217,$G$98,Sheet13!$H$98:$H$217)

Is there a quick way to add these up without having a monster formula? Any help would be appreciated.

 

[Richard Schollar]

Hi

Give this a try:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet" & ROW(INDIRECT("1:13")) & "!G98:G217"),G98,INDIRECT("Sheet" & ROW(INDIRECT("1:13")) & "!H98:H217")))

Regards

Richard

 

[Claymationator]

That worked great, thanks for the post!

Clay

 

[Claymationator]

Another question for you on that last post where you put in the formula. I have entered that in and it seems to work, but only when my sheets are left named as "Sheet 1" "Sheet 2" etc.

I had them as that, but then went in and changed the name of each sheet to be a week, and that obviously changed a few things, and now it does not work. The names that I have are as follows:

10-1 to 10-7-06
10-8 to 10-14-06
10-15 to 10-21-06
...and so on for the remainder of the year.

What would the formula be like that would work with the new names of the sheets? I have tried a few things, but am unable to figure it out.

 

[Domenic]

Enter the sheet names in a range of cells, let's say A2:A5, then try the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$5&"'!G98:G217"),G98,INDIRECT("'"&$A$2:$A$5"'!H98:H217")))

Hope this helps!

 

[Claymationator]

That worked! Thanks for the help, I really appreciate it.

Clay

 

[Superstar31]

Enter the sheet names in a range of cells, let's say A2:A5, then try the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$5&"'!G98:G217"),G98,INDIRECT("'"&$A$2:$A$5"'!H98:H217")))

Hope this helps!

Sweet Lord I love the search function!!!!