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
|