#VALUE! On An Array Formula Referencing a Range Outside The Workbo
 

This formula works only when i have the other referenced workbook open
simultaneously...when it is not open, i get the #VALUE! error....

Array Formula:
{=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value
Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss
Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)}

There are some functions that don't work with closed workbooks. You found one
with =sumif().

But you could use a different formula to replace the =sumif() portion:

=sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280),
'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95)



paige wrote:

This formula works only when i have the other referenced workbook open
simultaneously...when it is not open, i get the #VALUE! error....

Array Formula:
{=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value
Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss
Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)}

--

Dave Peterson

Ps. I'd open that other workbook and build the formula by pointing and
clicking. Excel will add the path when you close the workbook.

And you won't be able to use the whole column in that formula.

paige wrote:

This formula works only when i have the other referenced workbook open
simultaneously...when it is not open, i get the #VALUE! error....

Array Formula:
{=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value
Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss
Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)}

--

Dave Peterson

INTERESTING....THANKS. NEVER HEARD OF SUMPRODUCT....I LOVE LEARNING NEW ****.

THANKS AGAIN! ;O)

"Dave Peterson" wrote:

There are some functions that don't work with closed workbooks. You found one
with =sumif().

But you could use a different formula to replace the =sumif() portion:

=sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280),
'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95)



paige wrote:

This formula works only when i have the other referenced workbook open
simultaneously...when it is not open, i get the #VALUE! error....

Array Formula:
{=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value
Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss
Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)}

--

Dave Peterson

thanks...but how do i incorporate the if part of my formula? essentially, i
only need it to sum only when a condition is met.....do a sumproduct with a
match statement?

thoughts?

"Dave Peterson" wrote:

There are some functions that don't work with closed workbooks. You found one
with =sumif().

But you could use a different formula to replace the =sumif() portion:

=sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280),
'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95)



paige wrote:

This formula works only when i have the other referenced workbook open
simultaneously...when it is not open, i get the #VALUE! error....

Array Formula:
{=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gru ss Value
Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss
Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)}

--

Dave Peterson

paige wrote...
thanks...but how do i incorporate the if part of my formula? essentially, i
only need it to sum only when a condition is met.....do a sumproduct with a
match statement?

thoughts?
....

Reread what Dave told you to do. MATCH isn't needed.

SUMIF won't function if either its 1st or 3rd arguments are external
link references into closed workbooks. You can replace function calls
like

SUMIF(extref1,X,extref2)

with SUMPRODUCT calls like

SUMPRODUCT(--(extref1=X),extref2)

Replace the SUM(IF(..)) part of your formula with a SUMIF,

SUMIF($F$5:$F$92,$F280,O$5:O$92)

and replace the SUMIF part with SUMPRODUCT, at which point it wouldn't
need to be entered as an array formula.