SumIf multiple sheets/books

[SailorJerry7030]

Hello!

I'm trying to modify a SumIf to sum across multiple sheets (they are actually workbooks though). Below is the formula I have right now however it's not working. Example A and Example B are their own individual sheets in their own workbooks.

=sumproduct(SUMIF(Indirect("'"[Example A.xlsx]Schedule&[Example B.xlsx]Schedule"'!$U$13:$U$17"),$B40,Indirect("'"[Example A.xlsx]Schedule&[Example B.xlsx]Schedule"'!$V$13:$V$17)))


The goal here is to sum V13:V17 in Example A when criteria in B40 is met in range U13:U17 and to sum S13:S17 in Example B when criteria in B40 is met in range R13:R17. Obviously, I'm missing the ranges from Example B, but I was hitting my head against a wall and decided to come here for help.

I tried checking before and that's how I came across the sumproduct example but couldn't get it to work properly.

Any help is greatly appreciated!

 

[SailorJerry7030]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

will work as advertised if SheetList refers to a range housing one by one the names of the relevant sheets, all in the same workbook.

If SheetList refers to range housing workbook names like [Example A.xlsx]Schedule, I don't expect the above formula to work. (By the way, neither INDIRECT nor SUMIF work with closed workbooks.

Assuming the workbook is open then, a SumIF referencing an open workbook works fine. The same logic should be able to be applied to multiple workbooks, no? Assuming they are all open. If not, can you think of any other way to accomplish this? I can't connect to the workbooks through the Data tab at the top due to them being housed on Sharepoint and there are access issues getting in the way.

 

[Aladin Akyurek]

Assuming the workbook is open then, a SumIF referencing an open workbook works fine. The same logic should be able to be applied to multiple workbooks, no?
[...]

The answer is no. Having a bunch of sheets in one workbook is simply not the same thing as having a bunch of workbooks.

If not, can you think of any other way to accomplish this? I can't connect to the workbooks through the Data tab at the top due to them being housed on Sharepoint and there are access issues getting in the way.

Start a different thread with something like "conditional summing across multiple workbooks with vba" as topic.