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
|