|
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? |
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 |
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 |
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 |
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 |
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 |
I am attaching a complete list of what it looks like with all the different
formulas when open and when closed. VIEW external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) Cheers, "Dave Peterson" wrote: 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 |
Both =sum(if()) and =sumproduct() won't work with the whole column. So you have
to adjust your ranges if you want to get them to work. And this confuses me: external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) If the workbook is open, then you shouldn't be seeing the drive\path in the filename. Tunde wrote: I am attaching a complete list of what it looks like with all the different formulas when open and when closed. VIEW external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) Cheers, "Dave Peterson" wrote: 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 -- Dave Peterson |
I have restricted the =sum(if()) and =sumproduct() to look from row 1 to
2000. The list I included shows the results with and without the restriction of number of rows. What I mean by exerntal file open is that the workbook I am linking to is open and external file closed means the workbook is closed. I have to get my terminology right. "Dave Peterson" wrote: Both =sum(if()) and =sumproduct() won't work with the whole column. So you have to adjust your ranges if you want to get them to work. And this confuses me: external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) If the workbook is open, then you shouldn't be seeing the drive\path in the filename. Tunde wrote: I am attaching a complete list of what it looks like with all the different formulas when open and when closed. VIEW external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) Cheers, "Dave Peterson" wrote: 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 -- Dave Peterson |
I have restricted the column to rows 1 to 2000 and got zero as the value with
both =sum(if() and =sumproduct(). I converted the file path to text to show the results for the different scenarios and to show the formula I used to get that result, in case I did something wrong with one of my formulas. "Dave Peterson" wrote: Both =sum(if()) and =sumproduct() won't work with the whole column. So you have to adjust your ranges if you want to get them to work. And this confuses me: external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) If the workbook is open, then you shouldn't be seeing the drive\path in the filename. Tunde wrote: I am attaching a complete list of what it looks like with all the different formulas when open and when closed. VIEW external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) Cheers, "Dave Peterson" wrote: 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 -- Dave Peterson |
I don't think your other workbook was open. The formula includes your path and
that doesn't appear when the other workbook is open. So open your workbook with the formula. Then open the other workbook. Then back to one of the formulas. Copy it from the formula bar and paste it into your response. I really like the =sumproduct() version. If you have one of those, use that in your example. Tunde wrote: I have restricted the column to rows 1 to 2000 and got zero as the value with both =sum(if() and =sumproduct(). I converted the file path to text to show the results for the different scenarios and to show the formula I used to get that result, in case I did something wrong with one of my formulas. "Dave Peterson" wrote: Both =sum(if()) and =sumproduct() won't work with the whole column. So you have to adjust your ranges if you want to get them to work. And this confuses me: external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) If the workbook is open, then you shouldn't be seeing the drive\path in the filename. Tunde wrote: I am attaching a complete list of what it looks like with all the different formulas when open and when closed. VIEW external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) Cheers, "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
When you make a path text it shows the formula but doesn't calculate it. I
just put an ' in front of = and then showed the exact path I was linking to. These are the formulas that I have tried: =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) This one works and gives the correct value when the other workbook is open but as soon as I close it and update the link it says #VALUE - it's my original query. =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) This one shows #NUM! when the other workbook is open but gives a value of Zero(0) when it is closed. =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager -Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) This one shows #NUM! when the other workbook is open and also when it is closed. =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) This one shows zero value(0) when the file is open and when it is closed. =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) This one also shows zero (0) value when the other workbook is open and closed. =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) This one also shows zero value when the other workbook is open or closed. I am obviously missing something in my formulas, all the column and cell references are correct so not really sure why none of these are working. "Dave Peterson" wrote: I don't think your other workbook was open. The formula includes your path and that doesn't appear when the other workbook is open. So open your workbook with the formula. Then open the other workbook. Then back to one of the formulas. Copy it from the formula bar and paste it into your response. I really like the =sumproduct() version. If you have one of those, use that in your example. Tunde wrote: I have restricted the column to rows 1 to 2000 and got zero as the value with both =sum(if() and =sumproduct(). I converted the file path to text to show the results for the different scenarios and to show the formula I used to get that result, in case I did something wrong with one of my formulas. "Dave Peterson" wrote: Both =sum(if()) and =sumproduct() won't work with the whole column. So you have to adjust your ranges if you want to get them to work. And this confuses me: external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) If the workbook is open, then you shouldn't be seeing the drive\path in the filename. Tunde wrote: I am attaching a complete list of what it looks like with all the different formulas when open and when closed. VIEW external file open 23,069 =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #VALUE! =SUMIF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E,D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open #NUM! =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N)) external file open #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file closed #NUM! =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E:$E=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N:$N) external file open 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file closed 0 =SUM(IF('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2,'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)) external file open 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(--('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file open 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) external file closed 0 =SUMPRODUCT(('G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2)*'G:\GENERAL\Financial Manager - Andris\Budgets\Budget Preparation 2005 ~ 2006\[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) Cheers, "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Let's try to make it a little simpler.
First, open both workbooks. Second, don't convert the formula to text. Just copy it from the formula bar. (Select the whole formula and rightclick|Copy. Then paste into your post. But choose only one formula--if we get one working, the others might become much simpler. And choose a formula like: =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) In fact, try this against a smaller range =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10) And check your data to make sure that E1:E10 of that other worksheet actually equals D2 of the formula worksheet. Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. This formula worked for me when the other workbook was open or closed. ====== Remember =sum(if()) and =sumproduct() cannot use a whole column. <<snipped |
Hi Dave
I have made the range 1 to 200 as this will recognise all the rows that it's looking at. I don't understand why it keeps bringing back a zero value. I need it to look in one column in the other workbook and if the value matches that of the workbook the formula is in then I want it to bring back the value in another column in the other workbook. I need it to be a sum in case this more than one row with the same value. =SUMPRODUCT(--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$200=D$4),'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$200) "Dave Peterson" wrote: Let's try to make it a little simpler. First, open both workbooks. Second, don't convert the formula to text. Just copy it from the formula bar. (Select the whole formula and rightclick|Copy. Then paste into your post. But choose only one formula--if we get one working, the others might become much simpler. And choose a formula like: =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) In fact, try this against a smaller range =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10) And check your data to make sure that E1:E10 of that other worksheet actually equals D2 of the formula worksheet. Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. This formula worked for me when the other workbook was open or closed. ====== Remember =sum(if()) and =sumproduct() cannot use a whole column. <<snipped |
I'm gonna guess that the value in D$4 isn't the same as any of the values in
$e$1:$e:$200. If you type in a test value in D4 and then copy|paste in $e$1 (and put 99 in N1), do you get a nice answer. I think it's a data matching problem. Did you check for this? Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. Tunde wrote: Hi Dave I have made the range 1 to 200 as this will recognise all the rows that it's looking at. I don't understand why it keeps bringing back a zero value. I need it to look in one column in the other workbook and if the value matches that of the workbook the formula is in then I want it to bring back the value in another column in the other workbook. I need it to be a sum in case this more than one row with the same value. =SUMPRODUCT(--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$200=D$4),'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$200) "Dave Peterson" wrote: Let's try to make it a little simpler. First, open both workbooks. Second, don't convert the formula to text. Just copy it from the formula bar. (Select the whole formula and rightclick|Copy. Then paste into your post. But choose only one formula--if we get one working, the others might become much simpler. And choose a formula like: =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) In fact, try this against a smaller range =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10) And check your data to make sure that E1:E10 of that other worksheet actually equals D2 of the formula worksheet. Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. This formula worked for me when the other workbook was open or closed. ====== Remember =sum(if()) and =sumproduct() cannot use a whole column. <<snipped -- Dave Peterson |
The original Sumif is looking at the same cells and brings back a value when
the other workbook is open so that can't be it. Woohoo, it appears that column e wasn't a value, I changed it to be a value and it works. I can't believe it was such a simple fix. I didn't think the normal =sumif() would work if it wasn't a value. You're a genius. Thanks heaps. "Dave Peterson" wrote: I'm gonna guess that the value in D$4 isn't the same as any of the values in $e$1:$e:$200. If you type in a test value in D4 and then copy|paste in $e$1 (and put 99 in N1), do you get a nice answer. I think it's a data matching problem. Did you check for this? Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. Tunde wrote: Hi Dave I have made the range 1 to 200 as this will recognise all the rows that it's looking at. I don't understand why it keeps bringing back a zero value. I need it to look in one column in the other workbook and if the value matches that of the workbook the formula is in then I want it to bring back the value in another column in the other workbook. I need it to be a sum in case this more than one row with the same value. =SUMPRODUCT(--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$200=D$4),'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$200) "Dave Peterson" wrote: Let's try to make it a little simpler. First, open both workbooks. Second, don't convert the formula to text. Just copy it from the formula bar. (Select the whole formula and rightclick|Copy. Then paste into your post. But choose only one formula--if we get one working, the others might become much simpler. And choose a formula like: =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) In fact, try this against a smaller range =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10) And check your data to make sure that E1:E10 of that other worksheet actually equals D2 of the formula worksheet. Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. This formula worked for me when the other workbook was open or closed. ====== Remember =sum(if()) and =sumproduct() cannot use a whole column. <<snipped -- Dave Peterson |
Whew!
Glad you found the problem and fixed it. Tunde wrote: The original Sumif is looking at the same cells and brings back a value when the other workbook is open so that can't be it. Woohoo, it appears that column e wasn't a value, I changed it to be a value and it works. I can't believe it was such a simple fix. I didn't think the normal =sumif() would work if it wasn't a value. You're a genius. Thanks heaps. "Dave Peterson" wrote: I'm gonna guess that the value in D$4 isn't the same as any of the values in $e$1:$e:$200. If you type in a test value in D4 and then copy|paste in $e$1 (and put 99 in N1), do you get a nice answer. I think it's a data matching problem. Did you check for this? Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. Tunde wrote: Hi Dave I have made the range 1 to 200 as this will recognise all the rows that it's looking at. I don't understand why it keeps bringing back a zero value. I need it to look in one column in the other workbook and if the value matches that of the workbook the formula is in then I want it to bring back the value in another column in the other workbook. I need it to be a sum in case this more than one row with the same value. =SUMPRODUCT(--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$200=D$4),'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$200) "Dave Peterson" wrote: Let's try to make it a little simpler. First, open both workbooks. Second, don't convert the formula to text. Just copy it from the formula bar. (Select the whole formula and rightclick|Copy. Then paste into your post. But choose only one formula--if we get one working, the others might become much simpler. And choose a formula like: =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000) In fact, try this against a smaller range =SUMPRODUCT( --('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2), '[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10) And check your data to make sure that E1:E10 of that other worksheet actually equals D2 of the formula worksheet. Common problems are extra spaces (leading/trailing/embedded) or even numbers in one worksheet, but text (that look like numbers) in the other. This formula worked for me when the other workbook was open or closed. ====== Remember =sum(if()) and =sumproduct() cannot use a whole column. <<snipped -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 03:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com