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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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,377

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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,145
Members
410,666
Latest member
Al3cs
Top