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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!!!!
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top