SumIf Formulas and Links

[EdE]

When using the following sumif, if the sheet that it is linked to is not open, I get #Value! errors. But when it is open on my desktop, the errors go away, Any ideas?

=SUMIF('\serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$10:$FD$10,D$5,'\serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$138:$FD$138)

 

[Aladin Akyurek]

On 2002-10-03 17:15, EdE wrote:
When using the following sumif, if the sheet that it is linked to is not open, I get #Value! errors. But when it is open on my desktop, the errors go away, Any ideas?

=SUMIF('serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$10:$FD$10,D$5,'serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$138:$FD$138)

That the target workbook must be open is a requirement for SUMIF to work. If you insist on having a closed target workbook, you'll have to use a formula that operates on arrays:

=SUMPRODUCT(('serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$10:$FD$10)=D$5)*('serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$138:$FD$138))

which will affect the speed of your spreadsheet.

 

[EdE]

Ok now I still get the #value! error. What I am trying to do is find a text value in e10 through fd10, then sum the corresponding values in e138 through fd138. The text values in row 10 are always consecutive, however the range may change. I.E. sometimes Alpha is in E through AE, othertimes it may be CD through FD. Any other ideas? I couldnt get Sumproduct to work. I typed it in like sampled above.

Thanks
EdE

 

[Aladin Akyurek]

On 2002-10-03 17:50, EdE wrote:
Ok now I still get the #value! error. What I am trying to do is find a text value in e10 through fd10, then sum the corresponding values in e138 through fd138. The text values in row 10 are always consecutive, however the range may change. I.E. sometimes Alpha is in E through AE, othertimes it may be CD through FD. Any other ideas? I couldnt get Sumproduct to work. I typed it in like sampled above.

Thanks
EdE

Try whether this works:

=SUMPRODUCT(('serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$10:$FD$10)=D$5)+0,('serverfolderMaster Files[Master1-BS12x.xls]SD'!$E$138:$FD$138))