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
|