I am trying to link to another excel file using a sumif formula and get a
#value! if both files are not open. I have tried the Control Shift Enter and
changing the formula but am still not getting it. The formula is
=SUMIF([ACPS.xls]Monthly!$D$1:$R$1000,$D6,[ACPS.xls]Monthly!K:K). Any
suggestions?

=sumif() is one of the functions that doesn't work when the sending file is
closed.

=sumproduct(--([ACPS.xls]Monthly!$D$1:$R$1000=$D6),[ACPS.xls]Monthly!K1:K1000)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another is to use an array formula:
=SUM(IF([ACPS.xls]Monthly!$D$1:$R$1000=$D6,[ACPS.xls]Monthly!K1:K100))

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.)

You still can't use the whole column until xl2007.


RobertBMc wrote:

I am trying to link to another excel file using a sumif formula and get a
#value! if both files are not open. I have tried the Control Shift Enter and
changing the formula but am still not getting it. The formula is
=SUMIF([ACPS.xls]Monthly!$D$1:$R$1000,$D6,[ACPS.xls]Monthly!K:K). Any
suggestions?

--

Dave Peterson