SumIFS tweak

[Memar]

I have a formula using SUMIFS function to get a total sales amount based on three criteria from a different master workbook. It works only when the master workbook is open otherwise it gives me an error "#VALUE". Is there any tweak to make this formula works even if the master workbook is not opened every time I open the workbook which has this formula.

Formula Used - [FONT=&quot]=SUMIFS('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$L$2:$L$60000,'C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$B$2:$B$60000,$A$3,'C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$A$2:$A$60000,$E$4,'C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$W$2:$W$60000,$CI5)

Thanks for your time and help[/FONT]

 

[orsm6]

will using a SUMPRODUCT help you?. SUMIFS is a TRUE statement apparently and requires source workbook to be open.

 

[Aladin Akyurek]

Either, control+shift+enter, not just enter...

=SUM(IF(('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$B$2:$B$60000=$A$3)*('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$A$2:$A$60000=$E$4)*('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$W$2:$W$60000=$CI5),'C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$L$2:$L$60000))

Or, just enter...

=SUMPRODUCT('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$L$2:$L$60000,--('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$B$2:$B$60000=$A$3),--('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$A$2:$A$60000=$E$4),--('C:\Users\MMM\Desktop\Test 1\[MMM2.xlsx]Sheet1'!$W$2:$W$60000=$CI5))