SumIf Formulas and Links

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
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)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Apr 8, 2002
Messages
241
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))
 

Forum statistics

Threads
1,147,748
Messages
5,742,978
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top