sumifs using ID over multiple sheets

**Phillystones** · 05-05-2016, 11:42 AM

Good Morning,

I'm trying to build a sheet that will house a schedule as well as a statistics.
I decided to use Sumifs so that i could change the weekly schedules around and the data will automatically populate.

here is an example of the week 1 sheet.

A	B	C
Team ID	1WED	2WED
Match	1	1
Result	L	W
Points	13	16
2's	2	2
3's	1	2
6'd	0	0
Rounds	19	19

On the STATISTICS Sheet, my SUMIFS for their Offensive numbers is =SUMIFS(Week1!$B$5:$I$5,Week1!$B$1:$I$1,'Stats Page'!A3)+SUMIFS(Week2!$B$5:$I$5,Week2!$B$1:$I$1,'Stats Page'!A3)+SUMIFS(Week3!$B$5:$I$5,Week3!$B$1:$I$1,'Stats Page'!A3)..... ETC up to week 7 sheet. based on finding the TEAM ID: 1WED

What i'm looking for is how do i get the defensive numbers to populate. I need it to SUMIFS multiple sheets but to the opponent of 1WED which will change each week.

I hope this makes sense.

**FDibbins** · 05-05-2016, 12:01 PM

Hi, welcome to teh forum

It is almost always better if you can upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Having said that, there are ways to sum across sheets using named ranges and other functions. here is 1...
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{2,3,4}&"'!C5:P5"),"Hol"))….from Tony Valco
Obviously the ranges and criteria need to be changed

Another option that uses a list if sheet names and a dynamic range name...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!T6:T1000"),C794,INDIRECT("'"&Tabs&"'!M6:M1000")))
That probably means nothing to you, and, as it is, wont work for you, but when yopu upload your sample file, we can work on it

**Phillystones** · 05-05-2016, 01:42 PM

thanks for the help. Here is the file i'm referring too. I also Column H takes an average of games that aren't played yet. not sure how i can change the formula to counter that.