Sumif for multiple sheets within a workbook

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Give this a try:

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

Regards

Richard
 

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,719

ADVERTISEMENT

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!
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
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!!!!
 

Forum statistics

Threads
1,141,022
Messages
5,703,791
Members
421,316
Latest member
Cyril Beki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top