On May 25, 11:47*am, joeu2004 wrote:
On May 25, 6:52*am, pat67 wrote:

The issue is that if the file where the sumif is pulling
from is closed and I say filter the other file, i get the
#VALUE error. I was told that the sumif function has to
have the file open to see the data. I though it was an
issue with permissions or something. Which is true?

What you were told. *But why you would trust me any more than whoever
gave you the correct explanation in the first place? *Why don't you
just create a simple test and see for yourself?

Open a new workbook (Book1). *In the same instance of Excel, open a
second new workbook (Book2). *In Book2, enter the formula
=SUMIF([book1.xls]Sheet1!$A$1:$A$20,1). *Save and close both
workbooks, ideally first Book2, then Book1.

Now open just Book2. *If you get some warnings, just click OK. *You
will see that the SUMIF formula returns a #VALUE error. *Obviously,
that is not a permissions error because you created and saved both
files.

Now open Book1 in the same instance of Excel. *Look at the SUMIF
formula. *It might __still__ returns a #VALUE error(!). *In that case,
select the SUMIF cell, press F2, then Enter to "re-enter" the SUMIF
formula. *It should now return a valid number, probably zero if you
followed my instructions exactly.

A key point is to open both workbooks in the __same__ instance of
Excel. *Normally that happens automagically when you click on the file
icons. *But if you changed the default configuration, that might open
each file in separate instances of Excel. *It is difficult to tell the
difference visually, unless you are careful.

To be sure, click File Open in Excel instead of clicking the file
icons.

Try using SUMPRODUCT instead