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

[Guest]

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:\Gruss 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]

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)

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:\Gruss 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.

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:\Gruss 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)}

 

[Guest]

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

THANKS AGAIN! ;O)

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)

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:\Gruss 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)}

 

[Guest]

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?

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)

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:\Gruss 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)}

 

[Harlan Grove]

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.