SUMIF Not Calculating
 

i am using excel 2003 and am trying to use the SUMIF statement with the below
calculation:

=SUMIF('U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$B:$B,"Entered",'U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$H:$H)

As you can see i am trying to get information from another file. The
formula is sound only if the file i am getting the information from is open.
if the file that i am getting the information from is closed i get #VALUE!.
What do i need to do to get this formula to work to pull in the data with out
the source file being open.

SUMIF Not Calculating
 

now i am getting the #NUM! value showing up. the weird thing is that when i
changed t he formula back to the original it pulled in the correct value
without the file being opened which is what i want to do but when i closed
the master file and opened it back i up i came up with no results again. the
is crazy. Thanks for your help.

"daddylonglegs" wrote:

Try using SUMPRODUCT, although you'll have to use a defined range rather than
the whole column, e.g.


=SUMPRODUCT(--('U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$B1:$B1000="Entered"),'U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$H1:$H1000)

"Dlamon" wrote:

i am using excel 2003 and am trying to use the SUMIF statement with the below
calculation:

=SUMIF('U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$B:$B,"Entered",'U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$H:$H)

As you can see i am trying to get information from another file. The
formula is sound only if the file i am getting the information from is open.
if the file that i am getting the information from is closed i get #VALUE!.
What do i need to do to get this formula to work to pull in the data with out
the source file being open.

SUMIF Not Calculating
 

What formula are you trying? You'll get #NUM! error if you try to use whole
column refs like B:B or B1:B65536

"Dlamon" wrote:

now i am getting the #NUM! value showing up. the weird thing is that when i
changed t he formula back to the original it pulled in the correct value
without the file being opened which is what i want to do but when i closed
the master file and opened it back i up i came up with no results again. the
is crazy. Thanks for your help.

"daddylonglegs" wrote:

Try using SUMPRODUCT, although you'll have to use a defined range rather than
the whole column, e.g.


=SUMPRODUCT(--('U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$B1:$B1000="Entered"),'U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$H1:$H1000)

"Dlamon" wrote:

i am using excel 2003 and am trying to use the SUMIF statement with the below
calculation:

=SUMIF('U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$B:$B,"Entered",'U:\Operations Reports\[Open Orders
020607.xls]Sheet1'!$H:$H)

As you can see i am trying to get information from another file. The
formula is sound only if the file i am getting the information from is open.
if the file that i am getting the information from is closed i get #VALUE!.
What do i need to do to get this formula to work to pull in the data with out
the source file being open.