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!

 

[Aladin Akyurek]

See for SumIf(s) for across sheets (not across workbooks):

https://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

 

[SailorJerry7030]

Yeah that's what I came across when I was looking into it. Is it not possible to do across workbooks? Maybe I should ask there?

 

[Aladin Akyurek]

Yeah that's what I came across when I was looking into it. Is it not possible to do across workbooks? Maybe I should ask there?

I don't think this scheme works as workbooks.

 

[SailorJerry7030]

I don't think this scheme works as workbooks.

That's interesting because the below SumIF works fine across one workbook

=SUMIF('[Example C.xlsx]Schedule'!$Q$13:$Q$17,$B41,'[Example C.xlsx]Schedule'!$R$13:$R$17)

I would be surprised if you couldn't incorporate another sheet/workbook range to sum.

 

[Aladin Akyurek]

That's interesting because the below SumIF works fine across one workbook

=SUMIF('[Example C.xlsx]Schedule'!$Q$13:$Q$17,$B41,'[Example C.xlsx]Schedule'!$R$13:$R$17)

This is ok, as long as Example C.xlsx is not closed.

I would be surprised if you couldn't incorporate another sheet/workbook range to sum.

Another sheet is not a problem (see the link). By the way, INDIRECT also won't work with closed workbooks.

 

[SailorJerry7030]

So I referenced the link, that's actually where I started, but I can't seem to get the SumProduct formula to work. The code I have currently is

=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)))

Per the link, =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10"))) would be the formula I use, however I think I may have messed something up in my formula. I'm not sure where to put the range for Example A or Example B as they are 2 different ranges, but the example from the link only says A2:A10...what if I need a different range for the 2nd sheet?

 

[SailorJerry7030]

Shameless bump

 

[SailorJerry7030]

I tried to clean up my formula to fit better:

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

but getting the following error:

"There's a problem with this formula. Not trying to type a formula? etc etc etc"

Thoughts?

 

[Aladin Akyurek]

=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.