| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Sumif Linking to Another Workbook error #VALUE!
When I try to link a sumif to another workbook and I close the original
workbook I get #VALUE!, but as soon as I open the original workbook it works. How do I stop this happening? |
|
#2
|
|||
|
|||
|
Use a different function...
Maybe =sum(if(...)) or =sumproduct() will work ok for you Saved from a previous post (maybe you'll see how you can modify your existing formula): {=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green", 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))} =sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"), 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10) If this didn't help, post back with your existing formula. by the way, the =sum(if(... This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Tunde wrote: When I try to link a sumif to another workbook and I close the original workbook I get #VALUE!, but as soon as I open the original workbook it works. How do I stop this happening? -- Dave Peterson |
|
#3
|
|||
|
|||
|
Hi Dave
I tried both =sum(if and =sumproduct and got #NUM! instead. Someone suggested that the formula was too long, if that is so, is there anyway to overcome this. Here's a copy of the formula =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$4,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J:$J) Thank you. Tunde "Dave Peterson" wrote: Use a different function... Maybe =sum(if(...)) or =sumproduct() will work ok for you Saved from a previous post (maybe you'll see how you can modify your existing formula): {=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green", 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))} =sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"), 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10) If this didn't help, post back with your existing formula. by the way, the =sum(if(... This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Tunde wrote: When I try to link a sumif to another workbook and I close the original workbook I get #VALUE!, but as soon as I open the original workbook it works. How do I stop this happening? -- Dave Peterson |
|
#4
|
|||
|
|||
|
You probably received the #Num! error because Sumproduct doesn't allow
entire column references (E:E). Try this, and adjust your rows accordingly: =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E1:$E65000=D$4)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J1:$J65000) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tunde" wrote in message ... Hi Dave I tried both =sum(if and =sumproduct and got #NUM! instead. Someone suggested that the formula was too long, if that is so, is there anyway to overcome this. Here's a copy of the formula =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$4,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J:$J) Thank you. Tunde "Dave Peterson" wrote: Use a different function... Maybe =sum(if(...)) or =sumproduct() will work ok for you Saved from a previous post (maybe you'll see how you can modify your existing formula): {=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green", 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))} =sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"), 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10) If this didn't help, post back with your existing formula. by the way, the =sum(if(... This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Tunde wrote: When I try to link a sumif to another workbook and I close the original workbook I get #VALUE!, but as soon as I open the original workbook it works. How do I stop this happening? -- Dave Peterson |
|
#5
|
|||
|
|||
|
RD
I tried your suggestion and it came back with no value at all, and yes I did try it in a cell where there should have been a value. I am getting a zero. "RagDyer" wrote: You probably received the #Num! error because Sumproduct doesn't allow entire column references (E:E). Try this, and adjust your rows accordingly: =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E1:$E65000=D$4)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J1:$J65000) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tunde" wrote in message ... Hi Dave I tried both =sum(if and =sumproduct and got #NUM! instead. Someone suggested that the formula was too long, if that is so, is there anyway to overcome this. Here's a copy of the formula =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$4,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J:$J) Thank you. Tunde "Dave Peterson" wrote: Use a different function... Maybe =sum(if(...)) or =sumproduct() will work ok for you Saved from a previous post (maybe you'll see how you can modify your existing formula): {=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green", 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))} =sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"), 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10) If this didn't help, post back with your existing formula. by the way, the =sum(if(... This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Tunde wrote: When I try to link a sumif to another workbook and I close the original workbook I get #VALUE!, but as soon as I open the original workbook it works. How do I stop this happening? -- Dave Peterson |
|
#6
|
|||
|
|||
|
Open that other file and see what your formula and its results look like.
And post back the formula that you're currently using. Notice that the =sum(if( function that I posted isn't the same as =sumif() that you posted. Tunde wrote: RD I tried your suggestion and it came back with no value at all, and yes I did try it in a cell where there should have been a value. I am getting a zero. "RagDyer" wrote: You probably received the #Num! error because Sumproduct doesn't allow entire column references (E:E). Try this, and adjust your rows accordingly: =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E1:$E65000=D$4)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J1:$J65000) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tunde" wrote in message ... Hi Dave I tried both =sum(if and =sumproduct and got #NUM! instead. Someone suggested that the formula was too long, if that is so, is there anyway to overcome this. Here's a copy of the formula =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$4,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$J:$J) Thank you. Tunde "Dave Peterson" wrote: Use a different function... Maybe =sum(if(...)) or =sumproduct() will work ok for you Saved from a previous post (maybe you'll see how you can modify your existing formula): {=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green", 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))} =sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"), 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10) If this didn't help, post back with your existing formula. by the way, the =sum(if(... This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Tunde wrote: When I try to link a sumif to another workbook and I close the original workbook I get #VALUE!, but as soon as I open the original workbook it works. How do I stop this happening? -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Workbooks | Excel Worksheet Functions | |||
| linking cell value in one workbook to a cell in another workbook | Links and Linking in Excel | |||
| Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) | |||
| linking worksheets in same workbook | Excel Discussion (Misc queries) | |||
| Linking Data between worksheets in a workbook | Excel Worksheet Functions | |||
Hybrid Mode
