modify SUMIF to work w/ closed files

**Mathiasben** · 06-26-2013, 11:19 AM

Hello,

I have been trying to come up with a way to get this formula _

=SUMIF('[linking file.xlsx]Testing'!$A$7:$A$169,A5,'[linking file.xlsx]Testing'!$B$7:$B$169)

to work with closed files, is there a way to do the same thing using SUMPRODUCT? I'm not great w/ excel and would appreciate any help.

thanks so much!!

**daddylonglegs** · 06-26-2013, 11:21 AM

Try this version

=SUMPRODUCT(('[linking file.xlsx]Testing'!$A$7:$A$169=A5)+0,'[linking file.xlsx]Testing'!$B$7:$B$169)

**Fotis1991** · 06-26-2013, 11:22 AM

=SUMPRODUCT(('[linking file.xlsx]Testing'!$A$7:$A$169=A5)*('[linking file.xlsx]Testing'!$B$7:$B$169)) ??

**benishiryo** · 06-26-2013, 11:22 AM

hi Mathiasben, welcome to the forum. maybe:
=SUMPRODUCT(--('[linking file.xlsx]Testing'!$A$7:$A$169=A5),'[linking file.xlsx]Testing'!$B$7:$B$169)

or:
=SUMPRODUCT(('[linking file.xlsx]Testing'!$A$7:$A$169=A5)*('[linking file.xlsx]Testing'!$B$7:$B$169))