SUMIFS when data source is closed

[MPBJR]

After reading many threads I realize I can't use SUMIFS when the data source workbook is closed, but I just can't wrap my head around the SUMPRODUCT function, if that is what I should be using. I've posted my current SUMIFS formula below that works when the external data source is open.

I have 2 criteria it needs to check,
criteria 1-the date in the external source file column A needs to match the date in D2 of the new file
criteria 2-the shift number in the external source file column B needs to match the shift number in B5 of the new file

If those criteria match, I need the sum in the external source file column S divided by the sum in the external source file column AA


=IFERROR((SUMIFS('[\\folder\datasource.xlsm]SH 5'!$S$4:$S$4000,'[\\folder\datasource.xlsm]SH 5'!$A$4:$A$4000,D2,'[\\folder\datasource.xlsm]SH 5'!$B$4:$B$4000,$B$5))/(SUMIFS('\\folder\datasource.xlsm]SH 5'!$AA$4:$AA$4000,'[\\folder\datasource.xlsm]SH 5'!$A$4:$A$4000,D2,'[\\folder\datasource.xlsm]SH 5'!$B$4:$B$4000,$B$5)),"")

I'm hoping someone can help me wrap my head around this or at least point me in the right direction.

Thanks!

 

[Aladin Akyurek]

Re: Help using SUMIFS when data source is closed

Try...

=IFERROR(SUMPRODUCT('[\\folder\datasource.xlsm]SH 5'!$S$4:$S$4000/'[\\folder\datasource.xlsm]SH 5'!$AA$4:$AA$4000,--('[\\folder\datasource.xlsm]SH 5'!$A$4:$A$4000=D2),--('[\\folder\datasource.xlsm]SH 5'!$B$4:$B$4000=$B$5)),"")

 

[Aladin Akyurek]

Re: Help using SUMIFS when data source is closed

=IFERROR((SUMPRODUCT('[\\folder\datasource.xlsm]SH 5'!$S$4:$S$4000,—('[\\folder\datasource.xlsm]SH 5'!$A$4:$A$4000=D2),—('[\\folder\datasource.xlsm]SH 5'!$B$4:$B$4000=$B$5))/(SUMPRODUCT(['\\folder\datasource.xlsm]SH 5'!$AA$4:$AA$4000,—('[\\folder\datasource.xlsm]SH 5'!$A$4:$A$4000=D2),—('[\\folder\datasource.xlsm]SH 5'!$B$4:$B$4000=$B$5)),"")

 

[MPBJR]

Re: Help using SUMIFS when data source is closed

Thanks, that worked perfectly! Any chance you know of a similar formula that will give me an average of a range rather than the sum? The data would be in the same external workbook, just a different column range.

 

[Aladin Akyurek]

Re: Help using SUMIFS when data source is closed

Thanks, that worked perfectly! Any chance you know of a similar formula that will give me an average of a range rather than the sum? The data would be in the same external workbook, just a different column range.

Which one -- post #2 or #3 ?

 

[MPBJR]

Re: Help using SUMIFS when data source is closed

Thanks for the reply, I was able to figure it out.
Thanks again!

 

[Aladin Akyurek]

Re: Help using SUMIFS when data source is closed

Thanks for the reply, I was able to figure it out.
Thanks again!

Would you reply to the query nontheless?

 

[MPBJR]

Re: Help using SUMIFS when data source is closed

Post #3

 

[Aladin Akyurek]

Re: Help using SUMIFS when data source is closed

Post #3

Thanks for the info. I assume you are running an array formula with AVERAGE.