#### Claymationator

##### Well-known Member

- Joined
- Sep 26, 2006

- Messages
- 705

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.