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
|