=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 here:
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.
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?
