SUMIFS across multiple sheets

Hello all!

Needing some assistance with my SUMIFS formula (See Attached)
on sheet Data2 G2

can see me having problems with columns H:N on this sheet as well. (if have the time...)


any help/advice is greatly appreciated!

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by splitting data into what they consider a sensible arrangement, i.e. many sheets and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So put all your data on a single sheet and add an extra column which will contain whatever it is that the individual sheets represent. If these are days of the week then create a date column and record the 1st day of the week. Then you won't need any formulae at all. Just the PT.

I agree completely with Richard on this, it will be FAR simpler to run teh summaries if all data is in a single sheet.

I am only showing this because I spent some time on it, but with your layot, you would need a convoluted formula like this...
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2,3,4,5,6,7}&"'!a2:a20"),$F2,INDIRECT("'Sheet"&{1,2,3,4,5,6,7}&"'!"&CHAR(CODE("a")+COLUMNS($A$1:A1))&"2:"&CHAR(CODE("a")+COLUMNS($A$1:A1))&"20")))
copied down and across.

If youe fata was all in 1 sheet, with an added column to ID which "sheet" (or whatever the division is), then the formula would be shortened to this...
=SUMIFS(Sheet8!C$2:C$31,Sheet8!$B$2:$B$31,data2!$F2)

Or this

The sheet names listed in E2:E8

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E$2:$E$8&"'!A2:A25"),$F2,INDIRECT("'"&$E$2:$E$8&"'!"&CELL("address",B$2)&":"&CELL("address",B$25))))

In that case, you will need to create a list of all your sheet names, and give that list a range name (say, sheetnames). then use something like this...
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetnames&"'!A2:A200"),$F2,INDIRECT("'"&sheetnames&"CELL("address",B$2)&":"&CELL("address",B$25))))

If you need a hand creating the sheet names list, let me know

To create a list of sheet names, you will need to enable macros (go to File/Options/Trust Center/Trust Center Settings/Macro Settings/Enable all macros)

next, right-click on any empty cell and select Define Name,
call it Sheetnames (can be anything, really, but thats what I used)
in Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Close

Then, where you wan the list of sheets to be, copy this down....

=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

That will list all current - plus any new sheets added - that you have. You can then use that range in ther formula provided

Originally Posted by FDibbins

Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

That will list all current - plus any new sheets added - that you have. You can then use that range in ther formula provided

You need to make the Sheetnames formula volatile just in case you change a sheet name otherwise the INDEX formula won't update.

Here's the method I use:

https://www.excelforum.com/showthread.php?t=929969

Glad to have helped and thanks for the rep.